SOLVED

New Contributor

# Help with type of formula to use

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.

 Category Item Wt of item Item Wt of item Item Wt of item Item Wt of item Item Wt of item Accessory Box 200 Plastic bag 10 tag 2 cable tie 5 sticker 4 Accessory plastic bag 15 tag 5 string 1 box 50 sticker 3 Footwear Box 300 loose paper 8 cardboard 15 tag 2 Footwear dust bag 25 box 430 stuffing 183 sticker 3 tag 2

Help is very much appreciated here.

thank you!

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

# Re: Help with type of formula to use

``=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.