 SOLVED

# Need formula help for countifs and sumifs

Occasional Contributor

# Need formula help for countifs and sumifs

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

# Re: Need formula help for countifs and sumifs

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

# Re: Need formula help for countifs and sumifs

@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")`` # Re: Need formula help for countifs and sumifs

An alternative could be SUMPRODUCT.

=SUMPRODUCT((\$C\$2:\$C\$19=H3)*(\$F\$2:\$F\$19=\$J\$2)*(\$E\$2:\$E\$19<>\$I\$2)) # Re: Need formula help for countifs and sumifs

Thank you, @Harun24HR - this did the trick!
best response confirmed by nmlynch (Occasional Contributor)
Solution

# Re: Need formula help for countifs and sumifs

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