Advanced Apology
 

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

Advanced Apology
 

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Wilson" wrote in message
...
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

Advanced Apology
 

Hi,

Maybe

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

Mike

"Wilson" wrote:

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

Advanced Apology
 

Good point I forgot about blank cells

"Arvi Laanemets" wrote:

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Wilson" wrote in message
...
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

Advanced Apology
 

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.

Advanced Apology
 

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

and

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

returned ZEROS.

I am baffled

Advanced Apology
 

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Wilson" wrote in message
...
=SUMPRODUCT(--('Raw Data'!I4:I4902="NA"),--('Raw
Data'!D4:D4902="0"),--('Raw
Data'!D4:D4902<""))

and

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

returned ZEROS.

I am baffled

Advanced Apology
 

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" wrote:

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

and

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

returned ZEROS.

I am baffled

thanks
 

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?

Advanced Apology
 

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

Advanced Apology
 

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" wrote in message
...
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

Advanced Apology
 

Second one worked.... THANKS!!!

Advanced Apology
 

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

Regards,

Ron
Microsoft MVP (Excel)


"Wilson" wrote in message
...

Second one worked.... THANKS!!!

Advanced Apology
 

have yet to find an example of the formula I'm looking for....

If you have Excel 2007, the newer formula might be:

=COUNTIFS(
A1:A7,"NA",
B1:B7,0)

I like to break up the ranges onto different lines, and take advantage of
Ctrl+Shift+U to toggle the expansion of the formula bar.
--
HTH :)
Dana DeLouis


"Wilson" wrote in message ...

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

Advanced Apology
 

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