CountIf Question

[Alpruett]

I have a large table in which I need to try to count the number of times that
"ALP" or "SCF" occurs in various discontiguous cells. So, is it possible to
set a formula to count a couple of different values in discontiguous cells or
am I expecting way too much from Excel? Thanks for any help.
-Alison

 

[Bernard Liengme]

You have a table: that implies the cells are contiguous.
The text ALP or SCF is somewhere in this table?
Suppose the text is somewhere in column B
=COUNTIF(B:B,"ALP")+COUNTIF(B:B,"SCF")
best wishes

 

[FSt1]

hi
you're not expection too much. I don't think you can set a dual criteria but
you can search a table to count the number of occurances of something.
if you table were range A1:I500, then in a cell J1, put this....
=countif(A1:I500,J2)
In J2, put your criteria. you can over type it to change search criteria ie
enter ALP into J2 to get the number of occurances of that then enter SCF to
get the number of occurances of that.

Regards
FSt1

 

[T. Valko]

don't think you can set a dual criteria

=SUM(COUNTIF(A1:I500,{"ALP","SCF"}))

Or:

=COUNTIF(A1:I500,"ALP")+COUNTIF(A1:I500,"SCF")

 

[FSt1]

thank you.
i just learned something. or at least how to

regards
FSt1

 

[T. Valko]

You can use the same technique with SUMIF and multiple criteria:

=SUM(SUMIF(A1:A500,{"ALP","SCF"},B1:B500))

 

[Alpruett]

Thank you for all of the advice. However, my situation is that I need to be
able to count the number of times that "ALP" or "SCF" occurs in cells H4, K4,
O4, AA4, AD4, etc. So, I don't have contiguous cells at all. Can I count
multiple variables in non-contiguous cells?

 

[T. Valko]

Well, since your cells don't follow a pattern:

=SUMPRODUCT(--(T(INDIRECT({"H4","K4","O4","AA4","AD4"}))={"ALP";"SCF"}))