Need help with a SUMIFS formula I need to modify

Kelly Gabel

I am currently using this formula for some KPI reporting and it is working great. I am now being asked to also filter by another criteria (eg. Project Manager = John Smith)

How do I add that without breaking the formula?

Tagging my formula guru for visibility @Paul Newcome

Genevieve P.

Hi @Kelly Gabel

SUMIFS and COUNTIFS (plural) are really easy to add new criteria to! All you have to do is identify the {range} or column that houses the name, add a comma, then identify the name itself.

Ex:

=SUMIFS({ProjectHours}, {DailyLogDate}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022), {Project Manager Column}, "John Smith" )

=COUNTIFS({DailyLogDate}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022), {Project Manager Column}, "John Smith" )

If you have a column listing out your Project Managers in this sheet that has the formula, that's even easier. Instead of typing "John Smith" in quotes, you would just reference the cell:

=COUNTIFS({DailyLogDate}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022), {Project Manager Column}, [Project Manager]@row)

Cheers,

Genevieve

Kelly Gabel

Helps if I include the formula LOL

=SUMIFS({ProjectHours}, {DailyLogDate}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022))

same question for the following COUNTIFS formula actually, if it's different.

=COUNTIF({DailyLogDate}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022))

Genevieve P.

Hi @Kelly Gabel

SUMIFS and COUNTIFS (plural) are really easy to add new criteria to! All you have to do is identify the {range} or column that houses the name, add a comma, then identify the name itself.

Ex:

=SUMIFS({ProjectHours}, {DailyLogDate}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022), {Project Manager Column}, "John Smith" )

=COUNTIFS({DailyLogDate}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022), {Project Manager Column}, "John Smith" )

If you have a column listing out your Project Managers in this sheet that has the formula, that's even easier. Instead of typing "John Smith" in quotes, you would just reference the cell:

=COUNTIFS({DailyLogDate}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022), {Project Manager Column}, [Project Manager]@row)

Cheers,

Genevieve

Kelly Gabel

@Genevieve P. Thank you so much!! Turns out, my attempts were REALLY close!!! You saved my bacon!

Thanks again!

Genevieve P.

Haha no problem! 🥓