Countifs with Different Named Ranges

Chris Mondeau

Hello again all! I've run into an #INCORRECT ARGUMENT SET when trying to reference two ranges in a countifs() function.

I believe the error is because my Project Range only references to one column, and my {Visits} range includes 12 columns (so for functions that take two ranges: the range sizes don’t match for the function).

{Projects} = Project Column on project visits sheet

{Visits} = Visit 1:Visit 12 columns on the project visits sheet

What I was trying to do do was count all visits in the month of May for the year 2019 that were related to the project in that row. Before I added the additional condition of which project I was referencing the formula worked as expected. Then it broke with the project criteria (do to what I presume was the different range size).

Does anyone have any alternate suggestions to help me out?

Thanks!

Paul Newcome

The issue is that your range sizes do not match. You would need to add together 12 SUMIFS (1 for each of the Visit columns), or you can add 12 helper columns that contain a very basic

"=Project@row"

to duplicate that data into a 12 column grid that matches the same size as your Visit columns.

Stefan

Hi,

I think there is simply a logical condition missing.

=COUNTIFS({Project}, =Project@row...

Hope this helps

Stefan

Chris Mondeau

Hi @Stefan , thanks for the weekend reply! I tried your suggestion, but unfortunately still have the same issue.

Paul Newcome

The issue is that your range sizes do not match. You would need to add together 12 SUMIFS (1 for each of the Visit columns), or you can add 12 helper columns that contain a very basic

"=Project@row"

to duplicate that data into a 12 column grid that matches the same size as your Visit columns.

Stefan

Hi,

same thoughts here, so I second Pauls excellent post.

Sigh, stopped reading before my fist post when I saw the missing condition ;-)

Greetings

Stefan

Chris Mondeau

@Paul Newcome thank you sir!

Paul Newcome

@cmondo Happy to help. 👍️

@Stefan You actually don't need to = inside of the COUNTIFS.

=COUNTIFS({Range}, Project@row, .....................)

In the above it is implied that it is equal to Project@row.

Stefan

@Paul Newcome

Ah, never new this. Always something to learn. Thanks :-)

Paul Newcome

Sure thing. It is more of a personal preference than a necessity. I personally only use the = if I am also using an @cell reference. It is just what I am used to. Same with the greater or less than arguments. If I include one of those then I have to use "@cell" as well or it bugs me. Haha.