This script exports CSV data to an Excel file, formats it, and applies a SUMPRODUCT formula.

Show-ExcelSumProduct.ps1

$sumData = ConvertFrom-Csv @"
ProductName, VendorName, TotalSales
Macbook, Apple
Desktop, DELL
RAM, Lenovo
HDD, HCL
Laptop, IBM
Mouse, Acer
"@

$data = ConvertFrom-Csv @"
Product,IBM,DELL,HP,Lenovo,HCL,Apple,Acer
Laptop,619,737,671,390,203,214,656
Desktop,574,911,635,524,171,711,693
RAM,276,576,596,125,756,305,837
HDD,433,472,599,430,537,922,700
RAM,206,852,604,419,207,149,963
Keyboard,427,262,536,874,390,945,817
Macbook,675,263,646,843,257,597,450
Mouse,965,616,831,163,780,287,539
Laptop,329,160,672,490,260,970,763
Macbook,435,238,261,888,305,967,351
Desktop,329,936,138,430,701,911,898
RAM,831,300,814,606,559,698,749
"@

$xlfile = "$PSScriptRoot\spike.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

$sumData | Export-Excel $xlfile -AutoSize -StartRow 2 -StartColumn 5
$xlpkg = $data | Export-Excel $xlfile -AutoSize -StartRow 10 -StartColumn 5 -PassThru

Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range "E2:G2" -BackgroundColor black -FontColor white -Bold -AutoSize
Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range "E10:L10" -BackgroundColor black -FontColor white -Bold

$formula = '=SUMPRODUCT(($E$11:$E$22=E3)*($F$10:$L$10=F3)*($F$11:$L$22))'
Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range "G3:G8" -Formula $formula -NumberFormat "#,##0" -BackgroundColor Yellow

Close-ExcelPackage $xlpkg -Show