Hi,

I Need a formula to count all the positive values with an addtioonal
condition.

Example -
Column A Column B
100 A
200- A
300 A
400 B

In The above case i need the formula to get me the count all the
positive figures with a "A" in column B.


Thanks,
Thyag

Use a formula like the following:

=SUMPRODUCT((A1:A5>0)*(B1:B5="A"))

Change the A1:A5 and B1:B5 references to the appropriate ranges.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Thyag" <(E-Mail Removed)> wrote in message
news:5e3befb4-bca6-432d-96b4-(E-Mail Removed)...
> Hi,
>
> I Need a formula to count all the positive values with an addtioonal
> condition.
>
> Example -
> Column A Column B
> 100 A
> 200- A
> 300 A
> 400 B
>
> In The above case i need the formula to get me the count all the
> positive figures with a "A" in column B.
>
>
> Thanks,
> Thyag

something like to sum a above 0
=sumproduct((a2:a22>0)*(b2:b22="a")*(a2:a22))
to just count
=sumproduct((a2:a22>0)*(b2:b22="a"))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Thyag" <(E-Mail Removed)> wrote in message
news:5e3befb4-bca6-432d-96b4-(E-Mail Removed)...
> Hi,
>
> I Need a formula to count all the positive values with an addtioonal
> condition.
>
> Example -
> Column A Column B
> 100 A
> 200- A
> 300 A
> 400 B
>
> In The above case i need the formula to get me the count all the
> positive figures with a "A" in column B.
>
>
> Thanks,
> Thyag

Try something like this:

=SUMPRODUCT(ISNUMBER(A1:A10)*(A1:A10>0)*(B1:B10="A"))

I originally tried
=SUMPRODUCT((A1:A10>0)*(B1:B10="A"))

BUT....
if Col_A contains text (even an apostrophe)
with a corresponding "A" in Col_B

Example:
A5: Ron
B5: A

....That pair is counted.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

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

"Thyag" <(E-Mail Removed)> wrote in message
news:5e3befb4-bca6-432d-96b4-(E-Mail Removed)...
> Hi,
>
> I Need a formula to count all the positive values with an addtioonal
> condition.
>
> Example -
> Column A Column B
> 100 A
> 200- A
> 300 A
> 400 B
>
> In The above case i need the formula to get me the count all the
> positive figures with a "A" in column B.
>
>
> Thanks,
> Thyag

On Jan 7, 2:05*pm, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com>
wrote:
> Try something like this:
>
> =SUMPRODUCT(ISNUMBER(A1:A10)*(A1:A10>0)*(B1:B10="A"))
>
> I originally tried
> =SUMPRODUCT((A1:A10>0)*(B1:B10="A"))
>
> BUT....
> if Col_A contains text (even an apostrophe)
> with a corresponding "A" in Col_B
>
> Example:
> A5: Ron
> B5: A
>
> ...That pair is counted.
>
> Does that help?
> Post back if you have more questions.
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
> "Thyag" <thyagaraj.she...@gmail.com> wrote in message
>
> news:5e3befb4-bca6-432d-96b4-(E-Mail Removed)...
>
>
>
> > Hi,
>
> > I Need a formula to count all the positive values with an addtioonal
> > condition.
>
> > Example -
> > Column A * * * * Column B
> > 100 * * * * * * * * * *A
> > 200- * * * * * * * * * A
> > 300 * * * * * * * * * *A
> > 400 * * * * * * * * * *B
>
> > In The above case i need the formula to get me the count all the
> > positive figures with a "A" in column B.
>
> > Thanks,
> >Thyag- Hide quoted text -
>
> - Show quoted text -

Thanks all for your help.