SOLVED

Help with type of formula to use

%3CLINGO-SUB%20id%3D%22lingo-sub-3202193%22%20slang%3D%22en-US%22%3EHelp%20with%20type%20of%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3202193%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20massive%20spreadsheet%20(about%201000%2B%20lines)%20and%20have%20done%20a%20small%20sample%20of%20what%20the%20file%20looks%20like%20below.%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20count%20the%20number%20of%20cells%20that%20meet%202%20criteria.%20they%20are%3A%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20category%20%3D%20X%2C%20return%20the%20total%20count%20where%20item%20%3D%20A%3B%26nbsp%3B%3C%2FP%3E%3CP%3Ethere%20are%208%20categories%2C%20and%20about%2030%20different%20items%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20find%20the%20average%20weight%20based%20on%20the%20same%202%20criteria%3C%2FP%3E%3CP%3EIf%20category%20%3D%20X%20and%20item%20%3D%20A%2C%20return%20the%20sum%20of%20the%20value%20from%20the%20column%20%22Wt%20of%20item%22%26nbsp%3B%3C%2FP%3E%3CP%3E*%20the%20same%20item%20is%20NOT%20always%20in%20the%20same%20column.%20Eg%3A%20item%20%3D%20box%2C%20appears%20in%20different%20columns%20in%20the%20sample%20below.%20I%20cannot%20sort%20the%20file%20at%20this%20point%2C%20as%20I%20don't%20have%20the%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22110%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3ECategory%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWt%20of%20item%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWt%20of%20item%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWt%20of%20item%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWt%20of%20item%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWt%20of%20item%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E%3CP%3EAccessory%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EBox%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E200%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EPlastic%20bag%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Etag%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Ecable%20tie%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Esticker%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3EAccessory%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Eplastic%20bag%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Etag%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Estring%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Ebox%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E50%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Esticker%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3EFootwear%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EBox%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E300%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Eloose%20paper%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Ecardboard%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Etag%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3EFootwear%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Edust%20bag%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E25%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Ebox%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E430%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Estuffing%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E183%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Esticker%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3Etag%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20is%20very%20much%20appreciated%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3202193%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3203734%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20type%20of%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3203734%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1315222%22%20target%3D%22_blank%22%3E%40Jeanette1935%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUMPRODUCT((%24A%244%3A%24A%247%3DC12)*((%24B%244%3A%24B%247%3DD12)%2B(%24D%244%3A%24D%247%3DD12)%2B(%24F%244%3A%24F%247%3DD12)%2B(%24H%244%3A%24H%247%3DD12)%2B(%24J%244%3A%24J%247%3DD12)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%20This%20formula%20is%20in%20cell%20E12%20in%20the%20attached%20example.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUM(MMULT((%24A%244%3A%24A%247%3DC12)*(IF(%24B%244%3A%24B%247%3DD12%2C%24C%244%3A%24C%247%2C0)%2B(IF(%24D%244%3A%24D%247%3DD12%2C%24E%244%3A%24E%247%2C0))%2B(IF(%24F%244%3A%24F%247%3DD12%2C%24G%244%3A%24G%247%2C0))%2B(IF(%24H%244%3A%24H%247%3DD12%2C%24I%244%3A%24I%247%2C0))%2B(IF(%24J%244%3A%24J%247%3DD12%2C%24K%244%3A%24K%247%2C0)))%2CROW(1%3A1)%5E0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BThis%20formula%20is%20in%20cell%20F12%20in%20the%20attached%20example.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.

@Quadruple_Pawn thank you! it works!!