SOLVED

Need formula help for countifs and sumifs

Occasional Contributor

Hi - trying to create some formulas for countifs and sumifs.  I need to count and sum if the data meet specific criteria in a few different columns.  Specifically, I'd like to exclude certain things from being counted if they meet criteria in one column but not the other, but I am not having luck with my formulas.

 

Attaching a spreadsheet as a very simple example with my four related questions.  Grateful for any help you can provide!

 

Nancy

6 Replies

@nmlynch 

You didn't show your formulas, so I can't tell you what you've possibly have been doing wrong.

It's straight forward SUMIFS() and COUNTIFS().

 

I suppose every row is equal to 1 item since there is no distinct field "Item".

 

@nmlynch For count try-

=COUNTIFS($C:$C,$H4,$F:$F,$J$1,$E:$E,I$3)

For sum try-

=SUMIFS($D:$D,$C:$C,$H10,$F:$F,$J$1,$E:$E,I$3)

To hard code criteria try like-

=COUNTIFS($C:$C,$H4,$F:$F,"Conventional",$E:$E,"<>Regenerative")

Harun24HR_0-1672218338231.png

 

@nmlynch 

An alternative could be SUMPRODUCT.

 

=SUMPRODUCT(($C$2:$C$19=H3)*($F$2:$F$19=$J$2)*($E$2:$E$19<>$I$2))

sumproduct.JPG

Thank you, @Harun24HR - this did the trick!
best response confirmed by nmlynch (Occasional Contributor)
Solution
That is a great solution, @Quadruple_Pawn - I very much appreciate the alternative option!
Glad to know! If this help then please tick mark the answer so that other can know your current problem is solved.