Prev: How do I move contact info from excell to Outlook?
Next: Formula Help...
From: excelllllllll on 15 Apr 2010 17:25
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...


From: excelllllllll on 15 Apr 2010 17:47
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...
>
>
From: ck13 on 15 Apr 2010 18:03
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...
> >
> >
 | 
Pages: 1
Prev: How do I move contact info from excell to Outlook?
Next: Formula Help...