Help w/sheet summary using COUNTIFS and a number in a third column

jmo

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

David Tutwiler

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)

David Tutwiler

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.

jmo

@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?

Paul Newcome

This should do it for you:

=SUMIFS([# Defect: Mandatory + Technical]:[# Defect: Mandatory + Technical], Defect:Defect, 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7)

David Tutwiler

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)

jmo

That's the stuff, @David Tutwiler !!!!!

Formulas are gonna be the death of me.

Appreciate your responsiveness,

Jeff

David Tutwiler

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!