CountIf Question

  • Thread starter Thread starter Alpruett
  • Start date Start date
A

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
 
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
 
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
 
don't think you can set a dual criteria

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

Or:

=COUNTIF(A1:I500,"ALP")+COUNTIF(A1:I500,"SCF")
 
You can use the same technique with SUMIF and multiple criteria:

=SUM(SUMIF(A1:A500,{"ALP","SCF"},B1:B500))
 
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?
 
Well, since your cells don't follow a pattern:

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