Help with type of formula to use

New Contributor



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


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)



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


 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!!