| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Formula - Count Ticks | Excel Discussion (Misc queries) | |||
| Count Formula | Excel Discussion (Misc queries) | |||
| Count Formula | Excel Worksheet Functions | |||
| Trying to construct a count count formula | Excel Discussion (Misc queries) | |||
| Count formula help!! | Excel Discussion (Misc queries) | |||
Linear Mode
