Countifs with more than 2 or statements

[edost4]

Hi all,

The following formula works as intended:

Please Login or Register  to view this content.

However, I read that having more than two or statements inside the countifs() statement, will cause the wrong results, ie. I shouldn't do this:

Please Login or Register  to view this content.

How can I write such countifs() formula with 3+ or statements?

[Pete_UK]

It is the third one which is causing you problems - you can't put cell references inside an array constant.

Hope this helps.

Pete

[edost4]

Pete, I understand that- just not how to correct it. Do you think you can show how I can re-write this formula so it works?
Currently the best I can come up with is:
SUM(COUNTIFS(Data!$O:$O,">="&Leaderboard!I$202,Data!$O:$O,"<"&Leaderboard!J$202,Data!$DH:$DH,Leaderboard!$B203,Data!$HG:$HG,{"<>-","-","<>-"},Data!$HD:$HD,{"Q4 2018 Campaign","Q4 2018 Campaign",""},Data!$HE:$HE,""))+SUM(COUNTIFS(Data!$O:$O,">="&Leaderboard!I$202,Data!$O:$O,"<"&Leaderboard!J$202,Data!$DH:$DH,Leaderboard!$B203,Data!$HG:$HG,{"<>-","-","<>-"},Data!$HD:$HD,{"Q4 2018 Campaign","Q4 2018 Campaign",""},Data!$HE:$HE,">"&"Data!$HF:$HF))

[Pete_UK]

What is the last pair of terms meant to be doing?

Pete

[edost4]

@pete Last pair as in Data!$HE:$HE,">"&"Data!$HF:$HF? It is comparing two date columns, both in the same row in "Data" tab

[JeteMc]

Don't know that it will make a difference, however there looks to be an extra quotation after the ampersand: Data!$HE:$HE,">"&"Data!$HF:$HF
If that doesn't help, perhaps you could upload a desensitized/fictional sample demonstrating what you are trying to accomplish.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[edost4]

I fixed this with sumproduct