# COUNTIFS with multiple columns

✭✭✭✭✭

Hi

I'm using a COUNTIFS to count particular strings in a number of columns, and then also check that another string appears in another column. All columns are on another sheet.

It works when I count one string in a number of columns in the sheet only, and it also works when I count a particular string in one single column and a particular string in another single column, but when I try to count a particular string in a number of columns and a particular string in another single column, it doesn't.

Any ideas, thoughts?

Thanks

Cheers

Richard

• ✭✭✭✭✭✭

You cannot have multiple range sizes within the same function. How many columns does the multiple column range cover?

• ✭✭✭✭✭

Hi Paul

It’s five columns. Does that mean if I create another 4 dummy columns for the second one to make five, it should work?

Thanks

• ✭✭✭✭✭✭

Sort of. You would use a basic cell reference formula to replicate the data in the single column.

• ✭✭✭✭✭

Thanks Paul

I just added another four empty columns and reference the five (same number of columns as the first selection) and works a treat.

Thanks for the help.

• ✭✭✭✭✭✭

Technically the formula will not output any error messages, but have you confirmed the number it is outputting is correct?

• ✭✭✭✭✭✭

In the below screenshot, it is going to count where column A is "A" and where column 1 is "Y". If I expand my selection to be counting columns A & B, it is going to still compare A to 1 and B to 2. Since "Y" is not in column 2, those rows where "A" is in column B are not going to be included in my overall count. If I want all rows with "A" in columns A & B, I need to make sure that "Y" is in both columns 1 & 2.

You can see this below with the count of 4 vs 6 with the same formula but without the "Y" in column 2 in the first screenshot and with the "Y" in column 2 in the second screenshot.

=COUNTIFS(A:B, @cell = "A", [1]:[2], @cell = "Y")

VS

• ✭✭✭✭✭

Very interesting, I see what you mean… I was just grateful it was erroring! :)

so if I just do an ‘=1@row’ in col 2, and same in 3,4&5 that should work?

Although I’m referencing another sheet so its writing a column range, but in theory that’s what it is behind the reference.

Thanks

• ✭✭✭✭✭

It's working for me via the first method at the moment... do you think that could be because it's going out to another sheet and so compiles the result or something like that?

Thanks

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!