Link to home
Create AccountLog in
Avatar of LUIS FREUND
LUIS FREUND

asked on

Calculating two columns to get a positive and negative number

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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

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.
Avatar of LUIS FREUND
LUIS FREUND

ASKER

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
Please check the attached and let me know what is not working.
C--Users-lfreund-Documents-EE-CALC.xlsx
Hi Neeraj,

I lightheaded in yellow what is not working for me, see attachment.   Thanks!
C--Users-lfreund-Documents-EE-CALC.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Column N in your file contains the formula which include the ranges up to row30. Is that correct?
yes.... but is there a way to apply the formula to the full range which is to row 27531
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.


User generated image
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
My apologies for not being clear.....
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?
User generated imageC--Users-lfreund-Documents-EE-CALC-.xlsx
Green would be correct....
Then you should keep the formula in column M.
ah okay...looks good
Thank you so much Neeraj!  As always a tremendous help!!!
You're welcome Luis!