Count formula
 

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

Count formula
 

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

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

Count formula
 

Here's the formula for AA

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

And for BB:

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

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


"John" wrote:

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

Count formula
 

Thank you, Luke. It works.

"Luke M" wrote:

Here's the formula for AA

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

And for BB:

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

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


"John" wrote:

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

Count formula
 

Thank you, Jacob.

"Jacob Skaria" wrote:

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

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