Calculating two columns to get a positive and negative number

LUIS FREUND
LUIS FREUND used Ask the Experts™
on
Wanted to calculate two columns to get a positive and negative number.  On column L I've manually calculated the values that I'm trying to get.  

Wrote out explanations on the attachment to get the desired results and rationale.

See attachment
CALC-DEMAND.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Hi Luis,

Maybe you need this...
=IF(COUNTIF(F$3:F3,F3)<>COUNTIF([PN],F3),"",I3-SUMIF([PN],F3,[NOT ISSUED]))

Open in new window

and copy it down.

Author

Commented:
Thank you Neeraj!  It does work but I applied it to my data set and for some reason it does not like the column called "Part No".  I attached it for your review.  I believe it's how the column is formatted.
C--Users-lfreund-Documents-EE-CALC.xlsx
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Please check the attached and let me know what is not working.
C--Users-lfreund-Documents-EE-CALC.xlsx

Author

Commented:
Hi Neeraj,

I lightheaded in yellow what is not working for me, see attachment.   Thanks!
C--Users-lfreund-Documents-EE-CALC.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please have a look at the Sheet called Test where I have tweaked the existing formula in column L and placed a new formula in column M.
Let me know which one returns the correct output for you.
If it still doesn't return the desired output in some of the cell, please manually mock up the desired output for those cells in column N.
C--Users-lfreund-Documents-EE-CALC-.xlsx

Author

Commented:
Thanks....I added values to column N.  I also added the full data set as well.

See attachment.
C--Users-lfreund-Documents-EE-CALC-.xlsx
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Column N in your file contains the formula which include the ranges up to row30. Is that correct?

Author

Commented:
yes.... but is there a way to apply the formula to the full range which is to row 27531
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
What's your actual requirement?
Do you need consolidated stock qty for a part number or qty for a part number for the current row?
e.g. if you look at part number in F3 (031-00268), there are 35 occurrences of 031-00268 in the data set so what should be the stock qty for all of them? i.e. stock qty for individual row or consolidated stock qty? Sorry if I am missing the logic here.


Part-Number.jpg

Author

Commented:
I would need qty for the individual row.  It's also based on dates.  So for example a part no can show up for multiple months.  So if I choose Jan I would like to see the individual stock qty for that given month.

The data is set to ascending order by date and part no.  Then I would filter to a given month.
C--Users-lfreund-Documents-EE-CALC-.xlsx

Author

Commented:
My apologies for not being clear.....
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
It's still not clear.
Please look at the following image where you have multiple occurrences of same part number for January. Which output is correct? Yellow or Green?
C--Users-lfreund-Documents-EE-CALC.jpgC--Users-lfreund-Documents-EE-CALC-.xlsx

Author

Commented:
Green would be correct....
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Then you should keep the formula in column M.

Author

Commented:
ah okay...looks good

Author

Commented:
Thank you so much Neeraj!  As always a tremendous help!!!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Luis!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial