Help w/sheet summary using COUNTIFS and a number in a third column
Hi team  I'm looking for a summary formula that:
 Looks for Created by month, Defect is "true" and provides a total of those rows
 I tried using the following formula to get started but cannot figure out how to get a sum of all defects (from the # Defect: Mandatory + Technical column): =COUNTIFS(Defect:Defect, 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7)
How can I get a summary formula to show 6 for all rows that sum up the # Defect: Mandatory + Technical column created in July? I can figure out the rest of the months from there.
Thanks,
Jeff
Best Answer

Ah! You're missing one of the ranges. So the first range is the sum range and the second range is the first Criterion range (happens to be the same thing in your case). Try this:
=SUMIFS([# Defect: Mandatory + Technical]:[# Defect: Mandatory + Technical], [# Defect: Mandatory + Technical]:[# Defect: Mandatory + Technical], >0, Defect:Defect, 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7)
Answers

If your COUNTIFS formula works as you intended it (and I think it will) then I think this is as simple as changing your COUNTIFS to SUMIFS and setting your RANGE to the # Defect column. Instead of counting instances, it will sum the numbers in your #Defect column.

@David Tutwiler  thanks for the assist however, I'm still struggling with this formula  I need all conditions met in order to get a sum of defects in the month of July:
 Created in July
 Defect = true (or 1)
 # Defect: Mandatory + Technical column>0
I tried this formula but get the #INVALID OPERATION error:
=SUMIFS([# Defect: Mandatory + Technical]:[# Defect: Mandatory + Technical], >0, Defect:Defect, 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7)
The columns are highlighting properly  just cant get it to add up:
What am I missing?

This should do it for you:
=SUMIFS([# Defect: Mandatory + Technical]:[# Defect: Mandatory + Technical], Defect:Defect, 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7)

Ah! You're missing one of the ranges. So the first range is the sum range and the second range is the first Criterion range (happens to be the same thing in your case). Try this:
=SUMIFS([# Defect: Mandatory + Technical]:[# Defect: Mandatory + Technical], [# Defect: Mandatory + Technical]:[# Defect: Mandatory + Technical], >0, Defect:Defect, 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7)

That's the stuff, @David Tutwiler !!!!!
Formulas are gonna be the death of me.
Appreciate your responsiveness,
Jeff

Glad you got it going. Formulas are super powerful, but the slightest miscue in a ( or , can be the death of it. Stick with them, they're worth it!
Help Article Resources
Categories
Check out the Formula Handbook template!