Countifs #INCORRECT ARGUMENT SET

Desmond Kispert-Bostick

Hello, I'm trying to get the following countifs function to work but I'm getting the Incorrect Argument Set error: =COUNTIFS([Employee Name]:[Employee Name], "Name", Sunday:Saturday, "W")

For context I'm trying to count the number of days specific employees have worked YTD.

Jon Baier

Are you trying to count the number of times employee "Name" worked Saturday or Sunday? If so, what is the column that has your day of week names?

Count the number of time Name worked on Saturday.

=COUNTIFS([Employee Name]:[Employee Name], "Name", [Column with Day]:[Column with Day], "Saturday")

Count the number of times Name worked Saturday or Sunday

=COUNTIFS([Employee Name]:[Employee Name], "Name", [Column with Day]:[Column with Day], OR(@cell="Saturday", @cell= "Sunday"))

Paul Newcome

The problem is that you are referencing two different size ranges. One is a single column and the other is multiple columns.

If @Jon Baier's solutions don't work, can you explain in more detail how exactly your process is working and possibly provide some screenshots with sensitive/confidential data removed, blocked, or replaced with "dummy data" as needed?

From the sound of it, you have a column for each day of the week? If that is the case, try using a COUNTIFS for the Saturday column and a second COUNTIFS for the Sunday column and add them together.

=COUNTIFS([Employee Name]:[Employee Name], "Name", Sunday:Sunday, "W") + COUNTIFS([Employee Name]:[Employee Name], "Name", Saturday:Saturday, "W")

Joanna Waligora

As far as I can tell the Countifs Range argument can't span multiple columns i.e. [Sunday]:[Saturday] is not a valid Range.

Your best bet is likely a helper column that counts the number of W's across the row:

=COUNTIF([Sunday]@row:[Saturday]@row, "W")

Then you can aggregate:

=SUMIFS([Employee Name]:[Employee Name], "Name", [HelperColumn]:[HelperColumn])

There's probably a way to do it all in one shot, without the helper column, but it's Friday afternoon...

Paul Newcome

@Joanna Waligora If you do separate COUNTIFS and add them together it can be done without a helper column.

Joanna Waligora

@Paul Newcome I tried that, but it doesn't seem to give a row-level OR, just a cell-level OR (which, granted, may well be what Desmond is after).

Given a simplified scenario with just Sat & Sun...

Sat, Sun

W, W

W, W

W,

And assuming the idea is to add 1 to the count if the employee worked both or ANY of the weekend days. Desired answer is 3. With single countifs the answer is 2. Using + and two countifs, the answer is 5. Is there a formula that will deliver 3 without a helper column?

Paul Newcome

@Joanna Waligora Yes. You would count the Saturdays, and then only count Sundays where Saturday is blank and add the two together.

=COUNTIFS(Name:Name, "John Doe", Saturday:Saturday, "W") + COUNTIFS(Name:Name, "John Doe", Sunday:Sunday, "W", Saturday:Saturday, "")

Joanna Waligora

@Paul Newcome Right, I see. Thank you. This won't size well though. Let's say there are 7 days {Sat,...,Sun} and any day(s) can be blank. If there was a length(concatenate()) option that aggregated along a row, across columns this would be a breeze...actually, are there ANY horizontal aggregation functions?

Paul Newcome

@Joanna Waligora So your latest example would be a separate column for all 7 days of the week and you only want a count of 1 for each row that has a "W" regardless of whether it was in one column or multiple columns for that row?

Joanna Waligora

@Paul Newcome Yes...count 1 or count # of instances...either one.

Paul Newcome

Hmm... I am going to have to play around a little bit. I'll get back to you on that.

Paul Newcome

@Joanna Waligora For a larger scale, the best way to accomplish it is to use a helper column.

To get a count of 1 for each row that has a "W" in it regardless of whether it is in one column or many, I would suggest a checkbox column with

=CONTAINS("w", Monday@row:Sunday@row)

Then count the checkboxes.

To get a total count of how many cells have a "W", I would use a text/number helper column with something like this in it:

=COUNTIFS(Monday@row:Sunday@row, "W")

Then use a SUMIFS to total up by person.

If you are only comparing two columns though (smaller scale), using my above solution is pretty straightforward without having to use the helper column.

Joanna Waligora

@Paul Newcome Thank you. I appreciate you taking the time to break down the options.

@Desmond Kispert-Bostick Apologies for hijacking your thread; hopefully it wasn't completely off-topic.

Paul Newcome

@Joanna Waligora Sure thing!