Count formula

J

John

Hi All,

I have a problem below. Could somebody help, please. Thanks in advance.

Cust Amount
AA 5
AA 5
AA 5
AA 5
AA -5
AA -5
BB 7
BB 7
BB -7


AA - I want to be able to count if "AA" in range A2:A10 then count positive
amount minus negative amount in B2:B10. In this case, the answer is "2"

BB - The same thing with "BB". The answer is "1"

Thank you
John
 
J

Jacob Skaria

Try the below

=SUMPRODUCT((A2:A10="AA")*(SIGN(B2:B10)))

If this post helps click Yes
 
L

Luke M

Here's the formula for AA

=SUMPRODUCT((A2:A10="AA")*((B2:B10>0)-(B2:B10<0)))

And for BB:

=SUMPRODUCT((A2:A10="BB")*((B2:B10>0)-(B2:B10<0)))
 
J

John

Thank you, Luke. It works.

Luke M said:
Here's the formula for AA

=SUMPRODUCT((A2:A10="AA")*((B2:B10>0)-(B2:B10<0)))

And for BB:

=SUMPRODUCT((A2:A10="BB")*((B2:B10>0)-(B2:B10<0)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct or ????? 5
SUMPRODUCT 5
Filter formula 8
incremental counting based on criteria 4
Auto-numbering 10
How to avoid arrey formula? 5
Another Sumif problem 1
Index / Match Question 1

Top