COUNTIFS including

[ollyhughes1982]

Hi all,

I have the following formula in D14:

=IFERROR(IF(B14="","",COUNTIFS('All Completed Runs'!$C$4:$C$2003,"*"&A14&"*",'All Completed Runs'!$D$4:$D$2003,"South Africa",'All Completed Runs'!$FL$4:$FL$2003,"<>1")),"")

This should read 1, as I have completed an event (venue) which includes Walvis in its name on one occasion, as per B14. I know why this isn't working, because Walvis Bay parkrun is in Namibia and at the moment my formula is only catering for events in South Africa. Is there any way that I can add in to the formula some sort of 'OR' functionality so that it picks up events that have been completed within either Eswatini, Namibia or South Africa, rather than just in South Africa, as it is at present? Guessing it might be some sort of SUMPRODUCT solution?

Effectively, I need to have the 'All Completed Runs'!$D$4:$D$2003,"South Africa" part just add in Eswatini and Namibia as well. Is there a way to do this within COUNTIFS?

Thanks in advance,

Olly.

 

[ollyhughes1982]

Apologies, I believe I have now resolved this: I added in the multiple criterion within { } brackets and enclosed the COUNTIFS within a SUM function. Please see below:

=IFERROR(IF(B14="","",SUM(COUNTIFS('All Completed Runs'!$C$4:$C$2003,"*"&A14&"*",'All Completed Runs'!$D$4:$D$2003,{"Eswatini","Namibia","South Africa"},'All Completed Runs'!$FL$4:$FL$2003,"<>1"))),"")

Thanks again.