Advanced Apology

[Wilson]

I've been searching the internet for the past 1.5 days and have yet to find
an example of the formula I'm looking for.... so if this is easy, I apologize
in advance.

I want a count of the number of cells where column "I" values = "NA" and
column "D" values = "0". In the example below, only the 2nd & 5th rows would
meet the criteria.

I D
NA 5
NA 0
xx/xx/xx 0
xx/xx/xx 0
NA 0
xx/xx/xx 7
NA 3

 

[Arvi Laanemets]

Hi

=SUMPRODUCT(--($I$1:I100="NA"),--($D$1:$D$100=0),--($D$1:$D$100<>""))

 

[Mike H]

Hi,

Maybe

=SUMPRODUCT((I1:I7="NA")*(D17=0))
which checks for zero
or if 0 is really like in your post
=SUMPRODUCT((I1:I7="NA")*(D17="0"))

Mike

 

[Mike H]

Good point I forgot about blank cells

Hi

=SUMPRODUCT(--($I$1:I100="NA"),--($D$1:$D$100=0),--($D$1:$D$100<>""))

 

[Wilson]

Thanks for the quick answers guys....

Both methods are returning "0"

This is data that I copied out of MSP. Would it matter if the entire column
(where the "NA" or "xx/xx/xx" is stored) was formatted to be a date. There
are around 5000 records.

 

[Wilson]

=SUMPRODUCT(--('Raw Data'!I4:I4902="NA"),--('Raw Data'!D44902="0"),--('Raw
Data'!D44902<>""))

and

=SUMPRODUCT(('Raw Data'!I4:I4902="NA")*('Raw Data'!D44902="0"))

returned ZEROS.

I am baffled

 

[Arvi Laanemets]

Maybe you have all data as strings and there are leading/trailing spaces
(like "0 ", or "NA ")

 

[Mike H]

Hi,

If you looking for ZERO remove the quotes

Is NA typed in or is it an error #N/A returned by a formula?

Mike

 

[Wilson]

NA is a value populated by my copy paste from MSProject. I don't believe it
is considered an error (i.e. there is no # in front of the NA). After the
last post, I went to my 'raw data' worksheet and removed all "NA" values from
the "I" column and used the following formula:

=SUMPRODUCT(I5:I5000="NA")

It returned zero.
I then populated 3 fields with "a" and used
=COUNTIF(I5:I5000,"a")
and it returned 3, so it worked.

In response to your comment on "if you are looking for zero..." I am looking
for the value "0" not an unpopulated zero. Should I use quotes or no?

 

[Wilson]

I just Copied and replaced the NA values and I'm still getting zero

 

[Ron Coderre]

Try this:
=SUMPRODUCT(--(A2:A10="NA"),ISNUMBER(B2:B10)*(B2:B10=0))

or this:
=SUMPRODUCT((A2:A10="NA")*ISNUMBER(B2:B10)*(B2:B10=0))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Wilson]

Second one worked.... THANKS!!!

 

[Ron Coderre]

You're welcome.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)

 

[Bernd P]

Hello,

...
or this:
=SUMPRODUCT((A2:A10="NA")*ISNUMBER(B2:B10)*(B2:B10=0))
...

Funny. I thought of
=SUMPRODUCT(--(PROPER(A2:A10)="NA"),--(--(B2:B10)=0))

[Just in case of some " NA" or "NA " etc.]

Have fun,
Bernd