Countif?

[Dewayne Bien]

I have the following formula in a cell:
=COUNTIF(K5:K71,"A")+COUNTIF(K5:K71,"I")+COUNTIF
(K5:K71,"R")+COUNTIF(K5:K71,"B")+COUNTIF(K5:K71,"C")
My problem is, how do I get the formula to count multiple
instances of the alpha number in a cell and add it to the
total. Eg. if there is "AAAAA" or "AACIB" in a cell; to
count it as 5 instead of 1 in the total.
Thanks for the help!
Dewayne

 

[Peo Sjoblom]

Try this array formula

=SUM((LEN($K$5:$K$71)-LEN(SUBSTITUTE($K$5:$K$71,"A",""))),LEN($K$5:$K$71)-LE
N(SUBSTITUTE($K$5:$K$71,"I","")),(LEN($K$5:$K$71)-LEN(SUBSTITUTE($K$5:$K$71,
"R",""))),(LEN($K$5:$K$71)-LEN(SUBSTITUTE($K$5:$K$71,"B",""))),(LEN($K$5:$K$
71)-LEN(SUBSTITUTE($K$5:$K$71,"C",""))))

entered with ctrl + shift & enter

 

[Don Guillett]

If I understand
=SUMPRODUCT((D14={"A","I","etc"})*1)

 

[Daniel.M]

Hi,

The following ARRAY formula (Ctrl-Shift-Enter):
=SUM(0+ISNUMBER(FIND(MID(Range,TRANSPOSE(ROW(
INDIRECT("1:"&LEN(Range)))),1),"AIRBC")))

You may replace Range by K5:K71.

Regards,

Daniel M.

 

[Daniel.M]

Forget this one. It won't work.

Regards,

Daniel M.

 

[Daniel.M]

With the same idea (and using horiz array)

=SUM(LEN($K$5:$K$71)-LEN(SUBSTITUTE($K$5:$K$71,{"A","I","R","B","C"},"")))

Regards,

Daniel M.

 

[Peo Sjoblom]

Hmm! I thought I tried that already but got an error so I took the long
road.
I tried
=SUM(LEN($K$5:$K$71)-LEN(SUBSTITUTE($K$5:$K$71,{"A";"I";"R";"B";"C"},"")))
and I didn't even think of trying the horizontal
Nevertheless, much better (as usual when it comes from Daniel)

 

[Pingger]

Daniel,

I was following the thread above and I want to use it for
counting date occurences. In other words, in cells AA4:AJ4
I want to count the occurence of dates in August of 2002?

TIA

Ping

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--(MONTH(AA4:AJ4)=8))

 

[Pingger]

Sweet!!! Thanks, Peo.

-----Original Message-----
Try

=SUMPRODUCT(--(MONTH(AA4:AJ4)=8))

--

Regards,

Peo Sjoblom





.

 

[Pingger]

Peo,

Is there a way to make it differentiate years? In other
words, it only counts the August2002 occurences in an
Aug2002 cell and August 2003 occurences in an Aug 2003
cell?

 

[Daniel.M]

Hi,

For the month of august 2002:

=SUMPRODUCT(--(AA4:AAJ4-DAY(AA4:AAJ4)=DATE(2002,8,)))

Regards,

Daniel M.

 

[Pingger]

Thanks for the tip, Daniel.

Maybe I should be more detailed with my question.
My data is in X4:AJ4 and is in _MMDDYYY_ format.
On Cell B4 I need to count the occurence of August 2002,
in C4 Sept 2002, D4 Oct02.....through......Oct2003.

For some reason the above formula doesn't count Aug 5,
2002 which is in X4 and Aug 10, 2002 in Y4. What is
missing?

Thanks in advance for your help.