Excel - How to select multiple areas as function parameter?

Question

Hello,

Let's say I have, for example, these two functions

=COUNTIF(List1!D:D;"Example") + COUNTIF(List2!D:D;"Example")

It would make more sense to write it in one function. But, how do I select multiple areas in the function parameter?

I partially succeeded in this way.

=COUNTIFS(D:D:E:E;"Example")

However, I can only select areas from the sheet itself, unfortunately this entry no longer works.

=COUNTIFS(List1!D:D:List2!D:D;"Example")

---

Please help

Thank you

Answer 1

Dear MichalRama

If your requirement is to select multiple areas in different sheet, you can add the SUM formula in the formula =SUM(COUNTIFS(INDIRECT({"'P'!D:D";"'R'!D:D"}),"Example")) like the screenshot below:

Sincerely,

Stacey | Microsoft Community Moderator

Answer 2

Please try to use the formula =COUNTIFS(INDIRECT({"'P'!D:D";"'R'!D:D"}),"Example") to check if it can work in your end.

Hello,

Thanks, but unfortunately this doesn't work either. Excel won't even let me use it because I have to separate the parameters with a semicolon.

I just forgot to mention that Excel will confirm my entry, but it will only display #SPILL!

What exactly does that mean please?

Answer 3

Dear MichalRama

Please try to use the formula =COUNTIFS(INDIRECT({"'P'!D:D";"'R'!D:D"}),"Example") to check if it can work in your end.

Sincerely,

Stacey | Microsoft Community Moderator

Answer 4

Hello,

I already managed to partially figure it out using the INDIRECT function

=COUNTIFS(INDIRECT("'P'!D:D");"Example")

The problem is that I didn't understand how to write curly braces for multiple sheets. This doesn't work.

=COUNTIFS(INDIRECT({"'P'!D:D";"'R'!D:D"});"Example")

What is the correct entry, please?

Thank you

Answer 5

=COUNTIFS(D:D:E:E;"Example")

works because D:D:E:E is in fact equal to the single range D:E.

Functions such as SUMIF, SUMIFS, COUNTIF etc. don't work with multi-sheet references.

See Count occurrences in entire workbook or a workaround.