COUNTIFS with multiple columns

Richard

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

Paul Newcome

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

Richard

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

Paul Newcome

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

Richard

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.

Paul Newcome

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

Paul Newcome

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

Richard

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

Richard

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