Need help with a sumifs formula

McAfee Electric Mgr · 10/20/21

I need to figure out why my "sumifs" formula is not working. I want to total a duration column if a payroll Item column contains one of 3 possible choices. My formula is as follows...

=SUMIFS(Duration:Duration, Payroll Item:Payroll Item, "Hourly",[Payroll Item:Payroll Item, "Overhead Hourly"], [Payroll Item:Payroll Item, "Officer Hourly"])

Not sure what is wrong with this formula or why it will not work.

Thanks!

Genevieve P. · 10/22/21

Hi @McAfee Electric Mgr

There's a couple things to note here. The first is that whenever you have a column name with a space in it or numbers, you'll need to enclose the name in [these].

For example:

Payroll Item:Payroll Item

Should be:

[Payroll Item]:[Payroll Item]

See: Create a Cell or Column Reference in a Formula

The next thing is that since you're looking for multiple possible options, instead of listing them in a row like that (which means "AND", so it will exclude cells that only contain one option), we'll want to add in an OR statement and use @cell as the range in the OR statement, like so:

[Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell = "Overhead Hourly", @cell = "Officer Hourly")

Full formula:

=SUMIFS(Duration:Duration, [Payroll Item]:[Payroll Item], OR(@cell = "Hourly", @cell = "Overhead Hourly", @cell = "Officer Hourly"))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P. · 10/22/21