Log In
Register
Home
February 2, 2013
Offline
Hi every one,
I have tried Sumifs formula but i cant get the closing qty details in opening column once i update issued qty by manually highlighted in yellow this formula i need based on (C13) & (B13), can any body help to solve this.
Regards/Saliha
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Saliha
The value in cell B42 (23) is different from the others in column B (8).
Sunny
February 2, 2013
Offline
Hi SunnyKow
yes it is different i have mistakenly updated, but i have to updated based on "B" column & "C" column opening.
Saliha
VIP
Trusted Members
December 7, 2016
Offline
Hello Saliha,
In my view you make it more difficult than it needs to be by having this Opening Qty column, there is no need for it as you have the number you want in the Closing Qty column. You just need to write another formula for the Closing Qty.
Try with following SUMPRODUCT formula in cell N4 and copy down.
=SUMPRODUCT(($J$4:$J4-$M$4:$M4)*($B$4:$B4=$B4)*($C$4:$C4=$C4))
By summing the Closing Qty per the two criterias you no longer need to have the Opening Qty column. See if this works for you.
Br,
Anders
February 2, 2013
Offline
Hi Anders
Thanks for your reply, but its showing wrongly opening data's displaying, i have attached for your reference.
regards/Saliha
VIP
Trusted Members
December 7, 2016
Offline
Hello Saliha,
The formula I gave is to be used in column N, the Closing Qty column. I suggest you delete the Opening Qty column. I am sorry if I was not clear about that.
Is there any reason to why you need the Opening Qty column other than to keep the current formula in column N?
Br,
Anders
VIP
Trusted Members
June 25, 2016
Offline
Hi Saliha
See if this helps.
Sunny
Answers Post
February 2, 2013
Offline
Hello SunnyKow
Exactly i need this formula thanks for you, but i need lookup value b4 & c4 which is you have updated C column only.
can you please help to modify B column and C column.
=IFERROR(LOOKUP(2,1/($C$4:$C34=C35),$N$4:$N34),0)
Can you please tell me what is the logic you have use this (2,1/
Saliha
VIP
Trusted Members
June 25, 2016
Offline
Hi Saliha
The normal LOOKUP search for the 1st occurrence of the value.
This LOOKUP searches for the last occurrence of the value.
I don't understand your question : "I need lookup value b4 & c4 which is you have updated C column only"
Please provide examples and the expected results.
Sunny
VIP
Trusted Members
December 7, 2016
Offline
Hello Saliha,
A modified formula with two criterias using the LOOKUP function. You can replace the 1E+100 with 3 if you want. To be put in cell K4. Here is a good article for some guidance in the logic used in the formula.
=IFERROR(LOOKUP(1E+100,(1/($B$3:$B3=$B4))+(1/($C$3:$C3=$C4)),$N$3:$N3),0)
You can also use XLOOKUP to get the same result, easier to read. To be put in cell K4.
=XLOOKUP($B4&$C4,($B$3:$B3)&($C$3:$C3),$N$3:$N3,0,0,-1)
But again, based on your sample file I don't see any reason to have this Opening Qty column, if the goal is to have correct Closing Qty value based on these two criterias then that can be achieved without having the Opening Qty column as a helper column.
Br,
Anders
1 Guest(s)
