Using SUMIF for more than one column

Pamela Wagner

I have a time tracker that I am trying to get metrics on for certain categories (Admin, Tickets, Projects). We note what the category is then to the right under departments, we note the amount of time spent per department. I can get my formula to work if I have it look at only one column but not if I give it a series of columns:

=SUMIF(Category3:Category315, "Tickets", ITAdmin4:TC314)

Here are the departments:

So I need for it to look first in cell ITAdmin1 then go through to the end of the sheet to TC314. It's giving me an incorrect argument set. Am I using the wrong formula type?

Genevieve P.

Hi @Pamela Wagner

The way I would do this is to add a helper column at the end of your departments that does the individual SUM per-row:

=SUM(ITAdmin@row:TC@row)

Set this as a column formula so that each row has a total calculation listed.

Then for your SUMIF, you'll use the Helper Column as the source for your SUM, but only if the row says "Tickets":

=SUMIF(Category:Category, "Tickets", [Helper Column]:[Helper Column])

Keep in mind that when you list ranges in a formula they need to be the same size... for example, if you list Category1:Category3 (from row 1 to row 3) then you would need to list the same rows in your other range, like so: [Helper Column]1:[Helper Column]3

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Pamela Wagner

The way I would do this is to add a helper column at the end of your departments that does the individual SUM per-row:

=SUM(ITAdmin@row:TC@row)

Set this as a column formula so that each row has a total calculation listed.

Then for your SUMIF, you'll use the Helper Column as the source for your SUM, but only if the row says "Tickets":

=SUMIF(Category:Category, "Tickets", [Helper Column]:[Helper Column])

Keep in mind that when you list ranges in a formula they need to be the same size... for example, if you list Category1:Category3 (from row 1 to row 3) then you would need to list the same rows in your other range, like so: [Helper Column]1:[Helper Column]3

Let me know if this works for you!

Cheers,

Genevieve

Pamela Wagner

It worked! Thanks much!