Feb 22 2022 07:44 PM
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!
Feb 23 2022 05:00 AM
Solution=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.
Feb 23 2022 05:00 AM
Solution=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.