CountIf Function

D

David Harrison

HI there

I have a current count IF function that works well. until now.

The range is D4:D9,

Usually it only searches numbers but now i need it to search 8a and 8b as
values. Not the cells.

What is the way around this.

It would be more preferable to seach and add up 8a and 8a as values. (not
the cells 8a and 8b)

Thanks

David
 
L

Leo Heuser

Hi David

One way:

=COUNTIF(D4:D9,"8a")

and for counting the numbers of 8a AND 8b

=SUMPRODUCT(COUNTIF(D4:D9,{"8a,"8b"}"))
 
B

Bob Phillips

and for counting the numbers of 8a AND 8b

=SUMPRODUCT(COUNTIF(D4:D9,{"8a,"8b"}"))
Click to expand...

Typos apart, why introduce a nested function call?

=SUMPRODUCT(--(D4:D9={"8a","8b"}))
 
L

Leo Heuser

Bob Phillips said:
Typos apart, why introduce a nested function call?
Click to expand...

Good question! I guess, I was caught by the subject line.

How on earth did that quotation mark land after "}" instead of after "a" :)
 
L

Leo Heuser

Hi Bob

The expression

--(D4:D9={"8a","8b"})

returns a 2-dimensional (6 x 2) array

where

COUNTIF(D4:D9,{"8a,"8b"}")

returns a 1-dimensional (1 x 2) array

Either one might be useful depending on, which
functions are to be used on them.

To get the sum, either one may be used, and my
solution may be less memory hungry than yours,
depending on how Excel treats the functions internally.
OTOH it uses one more function call.


LeoH
 
Top