Need formula help for countifs and sumifs

[nmlynch]

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

[#2]

@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".

[#3]

@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")

[#4]

@nmlynch 

An alternative could be SUMPRODUCT.

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

[#5]

Thank you, @Harun24HR - this did the trick!

[#6 · Accepted solution]

That is a great solution, @OliverScheurich - I very much appreciate the alternative option!

[#7]

Glad to know! If this help then please tick mark the answer so that other can know your current problem is solved.