CountIf Function
 

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

Hi David

One way:

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

and for counting the numbers of 8a AND 8b

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


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"David Harrison" skrev i en
meddelelse ...
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

"Leo Heuser" wrote in message
...


and for counting the numbers of 8a AND 8b

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

Typos apart, why introduce a nested function call?

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

"Bob Phillips" skrev i en meddelelse
...

"Leo Heuser" wrote in message
...


and for counting the numbers of 8a AND 8b

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

Typos apart, why introduce a nested function call?

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

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

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


"Bob Phillips" skrev i en meddelelse
...

"Leo Heuser" wrote in message
...


and for counting the numbers of 8a AND 8b

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

Typos apart, why introduce a nested function call?

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