=Countif question

  • Thread starter Thread starter AlanN
  • Start date Start date
A

AlanN

I am having a stupid attack...

I am trying to change the formula =COUNTIF($J43:$AC43,"=D") to one that looks for "D","N" and "ND". I have tried
=COUNTIF($J43:$AC43,"=or('D','DE','ND')")
=COUNTIF($J43:$AC43,"=or(D,DE,ND)")
=COUNTIF($J43:$AC43,"=or("D","DE","ND")")
=COUNTIF($J43:$AC43,"={"D","DE","ND"}")

What am I doing wrong???

TIA, Alan
 
Alan

Instead try:

=SUMPRODUCT(($J43:$AC43={"D","DE","ND"})+0)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"AlanN" <[email protected]> skrev i en meddelelse I am having a stupid attack...

I am trying to change the formula =COUNTIF($J43:$AC43,"=D") to one that looks for "D","N" and "ND". I have tried
=COUNTIF($J43:$AC43,"=or('D','DE','ND')")
=COUNTIF($J43:$AC43,"=or(D,DE,ND)")
=COUNTIF($J43:$AC43,"=or("D","DE","ND")")
=COUNTIF($J43:$AC43,"={"D","DE","ND"}")

What am I doing wrong???

TIA, Alan
 
Hi AlanN!

You're assuming that Excel will handle multiple conditions. It won't

In your case a simple answer is to add separate COUNTIF functions

=COUNTIF($J$43:$AC$43,"D")+COUNTIF($J$43:$AC$43,"DE")+COUNTIF($J$43:$A
C$43,"ND")
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
I am having a stupid attack...

I am trying to change the formula =COUNTIF($J43:$AC43,"=D") to one
that looks for "D","N" and "ND". I have tried
=COUNTIF($J43:$AC43,"=or('D','DE','ND')")
=COUNTIF($J43:$AC43,"=or(D,DE,ND)")
=COUNTIF($J43:$AC43,"=or("D","DE","ND")")
=COUNTIF($J43:$AC43,"={"D","DE","ND"}")

What am I doing wrong???

TIA, Alan
 
Hi Alan
formula works for me. Have you copied the formula or did you
change/adapt something. Maybe you can post your formula. Another reason
could be that there are #NA error in your range J43:AC43

Frank
 
=SUM(COUNTIF($J$43:$AC$43,{"D","DE","ND"}))

=SUMPRODUCT(--ISNUMBER(MATCH($J$43:$AC$43,{"D","DE","ND"},0)))

I am having a stupid attack...

I am trying to change the formula =COUNTIF($J43:$AC43,"=D") to one that
looks for "D","N" and "ND". I have tried
=COUNTIF($J43:$AC43,"=or('D','DE','ND')")
=COUNTIF($J43:$AC43,"=or(D,DE,ND)")
=COUNTIF($J43:$AC43,"=or("D","DE","ND")")
=COUNTIF($J43:$AC43,"={"D","DE","ND"}")

What am I doing wrong???

TIA, Alan
 
Hi Frank; I appreciate the help.
I played around with the formula and found that the formula presents the right answer if I limit the range to 3 cells.
In the first cell is the formula =SUMPRODUCT(--(C2:E2={"D","DE","ND"}))
3 D DE ND D ND


If I change the range in the formula to C2:G2, the answer I get is #NA:
#N/A D DE ND D ND


Can you see why that is?
Thanks, Alan


I have tried a couple of times, at work and
 
Hi Alan
what kind of Excel version are you using (especially which laguage
version). Looks like it's the separattor [,]. in my German version I
have to change {"D","DE","ND"} to {"D"."DE". "ND".}

Frank
 
Back
Top