SOLVED

Help with type of formula to use

Copper Contributor

Hi,

 

I have a massive spreadsheet (about 1000+ lines) and have done a small sample of what the file looks like below. 

1. count the number of cells that meet 2 criteria. they are: 

If category = X, return the total count where item = A; 

there are 8 categories, and about 30 different items; 

 

2. find the average weight based on the same 2 criteria

If category = X and item = A, return the sum of the value from the column "Wt of item" 

* the same item is NOT always in the same column. Eg: item = box, appears in different columns in the sample below. I cannot sort the file at this point, as I don't have the time. 

 

CategoryItemWt of itemItemWt of itemItemWt of itemItemWt of itemItemWt of item

Accessory

Box 200Plastic bag10tag 2cable tie 5sticker4
Accessoryplastic bag15tag 5string 1box 50sticker 3
Footwear Box 300loose paper 8cardboard 15tag2  
Footwear dust bag25box 430stuffing 183sticker 3tag2

 

 

Help is very much appreciated here.

 

thank you!

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Jeanette1935 

=SUMPRODUCT(($A$4:$A$7=C12)*(($B$4:$B$7=D12)+($D$4:$D$7=D12)+($F$4:$F$7=D12)+($H$4:$H$7=D12)+($J$4:$J$7=D12)))

Is this what you are looking for? This formula is in cell E12 in the attached example.

=SUM(MMULT(($A$4:$A$7=C12)*(IF($B$4:$B$7=D12,$C$4:$C$7,0)+(IF($D$4:$D$7=D12,$E$4:$E$7,0))+(IF($F$4:$F$7=D12,$G$4:$G$7,0))+(IF($H$4:$H$7=D12,$I$4:$I$7,0))+(IF($J$4:$J$7=D12,$K$4:$K$7,0))),ROW(1:1)^0))

 This formula is in cell F12 in the attached example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

@OliverScheurich thank you! it works!!

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Jeanette1935 

=SUMPRODUCT(($A$4:$A$7=C12)*(($B$4:$B$7=D12)+($D$4:$D$7=D12)+($F$4:$F$7=D12)+($H$4:$H$7=D12)+($J$4:$J$7=D12)))

Is this what you are looking for? This formula is in cell E12 in the attached example.

=SUM(MMULT(($A$4:$A$7=C12)*(IF($B$4:$B$7=D12,$C$4:$C$7,0)+(IF($D$4:$D$7=D12,$E$4:$E$7,0))+(IF($F$4:$F$7=D12,$G$4:$G$7,0))+(IF($H$4:$H$7=D12,$I$4:$I$7,0))+(IF($J$4:$J$7=D12,$K$4:$K$7,0))),ROW(1:1)^0))

 This formula is in cell F12 in the attached example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

View solution in original post