SumProduct counting negative numbers
 

I have 2 columns as follows:

A B
-$24.00 n/a
$200 n/a
$350 n/a
$65 9.30%
$32 n/a
n/a 5%


i need a function that will count the number of times column A appears with
n/a only ( so in this case its 4). I have this so far...i don't know how to
write it so its counts the negative value:
=SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a"))

I also need a formula that will count the occurrance of when column B has a
value when column A has an n/a. In this case the answer would be 1.
I hope that make sense...

SumProduct counting negative numbers
 

Correction:
i need a function that will count the number of times column A appears with
n/a in COLUMN B ( so in this case its 4). I have this so far...i don't know
how to
write it so its counts the negative value:
=SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a"))

"excelllllllll" wrote:

I have 2 columns as follows:

A B
-$24.00 n/a
$200 n/a
$350 n/a
$65 9.30%
$32 n/a
n/a 5%


i need a function that will count the number of times column A appears with
n/a only ( so in this case its 4). I have this so far...i don't know how to
write it so its counts the negative value:
=SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a"))

I also need a formula that will count the occurrance of when column B has a
value when column A has an n/a. In this case the answer would be 1.
I hope that make sense...

SumProduct counting negative numbers
 

Assuming that in in column A, there is either a number or n/a try this
formula =SUMPRODUCT(--(B1:B100="n/a"),--(A1:A100<"n/a")) this works if B1 -
B100 is n/a and so long A1 - A100 is not n/a, so if there is n/a in column B
and nothing in column A, it will still count.

For the second one, it is the reverse,
=SUMPRODUCT(--(A1:A100="n/a"),--(B1:B100<"n/a"))
Of course, if in column B, the value is 0%, it will still count. If you do
not want to count 0% if there is any, you will need to modify the formula.

"excelllllllll" wrote:

Correction:
i need a function that will count the number of times column A appears with
n/a in COLUMN B ( so in this case its 4). I have this so far...i don't know
how to
write it so its counts the negative value:
=SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a"))

"excelllllllll" wrote:

I have 2 columns as follows:

A B
-$24.00 n/a
$200 n/a
$350 n/a
$65 9.30%
$32 n/a
n/a 5%


i need a function that will count the number of times column A appears with
n/a only ( so in this case its 4). I have this so far...i don't know how to
write it so its counts the negative value:
=SUMPRODUCT(--(G5:G15=0),--(H5:H15="n/a"))

I also need a formula that will count the occurrance of when column B has a
value when column A has an n/a. In this case the answer would be 1.
I hope that make sense...