Dec 27 2022 10:59 PM - edited Dec 27 2022 11:02 PM
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
Dec 27 2022 11:15 PM
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".
Dec 28 2022 01:08 AM
@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")
Dec 28 2022 08:51 AM
An alternative could be SUMPRODUCT.
=SUMPRODUCT(($C$2:$C$19=H3)*($F$2:$F$19=$J$2)*($E$2:$E$19<>$I$2))
Dec 28 2022 05:02 PM
Dec 28 2022 05:03 PM
SolutionDec 28 2022 05:46 PM
Dec 28 2022 05:03 PM
Solution