If/OR and COUNTIF together

[scsuflyboy]

In cells e2:e51 there are several options available in a drop down list (pulled from cells U20:U35). I want to count the number of times the selections from U20,U26, U32 and U38 show up. I am not getting any results with the formula I have below...

=IF(OR(COUNTIF(E2:E51=U$20,E2:E51=U$26,E2:E51=U$32),""))

[TMS]

Maybe:

=COUNTIF(E2:E51,U$20)+COUNTIF(E2:E51,U$26)+COUNTIF(E2:E51,U$32)


Regards, TMS

[icestationzbra]

i don't know how robust this formula is in the context of your data, revert here in case of issues.

for textual values in cells U20, U26, U32 and U38:

=sum(countif($e$2:$e$51,t(indirect("u"&{20,26,32,38}))))

for numerical values in column U, change "t" to "n" (without quotes).

mind you, if you have duplicate (non-unique) values amongst cells U20, U26, U32 and U38, this formula will output incorrect result (basically, it will count those duplicate values multiple times).

1006

[Ace_XL]

SUMPRODUCT versions of the two suggested solutions

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[icestationzbra]

a couple of more suggestions (for non-contiguous cells) to eschew functions such as the volatile INDIRECT, and the restrictive N or T...

=sum(index(countif($e$2:$e$51,choose({1,2,3,4},$u$20,$u$26,$u$32,$u$38)),0))

i have read conflicting information about the limit on the number of arguments that CHOOSE can take (29 vs 254). hence, another option without any such known limitation:

=sum(countif($e$2:$e$51,lookup({20,26,32,38},row($u$20:$u$38),$u$20:$u$38)))

these two will not clam up in case they encounter both textual and numerical values in column U (the formula in post #3 could handle one or the other).

1008