COUNTIFS Syntax

Lee Nugent

I am really struggling with COUNTIFS syntax and hope someone can shed light on what I am doing wrong.

I have 3 sheets. Sheet1 has assets from company a, Sheet2 has assets from company b and Sheet3 is my metrics sheet that has the COUNTIFS code.

On Sheet3, I have a COUNTIFS statement that is supposed to count the number of "In Stock" items from Sheet1 column 1 and Sheet2 column 1. The syntax is:

=COUNTIFS({Sheet1 Range 1}, "In Stock", {Sheet2 Range 1}, "In Stock")

The above code results in #INCORRECT ARGUMENT SET.

If I split the above code into two separate COUNTIFS statements, then there is no problem so I know the data is right.

Any suggestions?

L_123

Your issue is that the ranges are a different size, but even if the ranges were the same size, the outcome would not be what you are trying to get.

=COUNTIF({Sheet1 Range 1}, "In Stock")+COUNTIF({Sheet2 Range 1}, "In Stock")

the above should get what you are looking for.

Lee Nugent

Thank you! It makes perfect sense now as the ranges are different sizes.

Paul Newcome

As a note...

Even if they were the same size, you cannot use cross sheet references pointing at two different sheets within the same function.

Lee Nugent

Ah, very good to know.