• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumif calculation difficulties

V

Villalobos

Active Member
Hello Experts,

I would like to ask some help regarding a calculation problem (I think I should have to use the SUMIF formulae), I have uploaded the sample file. The short explanation to the problem can be read in the file. My target is that to find a formulae in column C.

Thanks in advance the reply!
 

Attachments

  • Sumif difficulties_Chandoo_org.xlsx
    11.4 KB · Views: 26
Hi, Villalobos!

In the posted formula, try changing this:
'Sheet2'!$A$1:$B$3
by this:
'Sheet2'!$A$1:$A$3

Despite of this, I get different values for column C. Even if it'd work as you expect, surely it needs the previous tweak, at least.

Regards!

PS: Next time please post here the issue description, keeping it into your workbook too if you consider it propertly.
This will help users who read this to decide whether they'd be able or want to analyze it further, without having to download/open any file.
 
Hello SirJB,

Thank you for your time!
Unfortunately this modification doesn't help.

You are right, I will keep it in mind.
 
=IFERROR(SUMIF(Sheet2!$A$1:$A$3,Sheet1!D2,Sheet2!$B$1:$B$3)-(SUMIF($D$2:$D2,D2,$F$2:$F2)),-(F2))

Works fine and give the correct results...
 
Dear Villalobos

As far as I can tell the logic behind the formula for C is that if there are non zero values in A and B then the formula is A-B otherwise if in column D there is an A or B then find the previous value in C for that value in D and subtract the value in column F and if there is a C in column D then find the previous value in column C for a C in D and subtract the value in column E.

=IF(AND(A2>0,B2>0),A2-B2,OFFSET($C$1,LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1,0)-OFFSET(E2,0,IF(D2<>"c",1,0)))

entered using ctrl+shift+entered works following the above logic
 
Dear Jake,

You are AWESOME, thank you for the reply, just I would have one remark.
I would like to use this formulae to handle the data in mass and this means that: in column D there would be more letter (character) than 3 than in the sample file. So if I write a new one letter into column D (e.g. W) the excel show #Ref! error message. How would it be change your formulae to avoid this error message?
 
DEar Villalobos

It would probably give an error if the first W didn't have values in the A and B columns, otherwise it would treat the W the same way as an A or B.
 
Hello Villalobos,
Can you clarify how column D is to factor into the calculations? In the sample workbook, the explanation on the right does not reference column D, whereas your sample formula does.

-Sajan.
 
Hello Sajan,

The target of the formulae is that to calculate the remaining stock after daily deliveries, so in column D can be found the materials. Column C will be a fix column and other cells of columns will be changed day by day.

Column A = Stock
Column B = Delivered quantity
Column C = Remaining stock after deliveries
Column D = Material
Column E = Ordered quantity
Column F = Open ordered quantity after deliveries
 
Hi Villalobos,
If your question has been answered by Jake, please feel free to ignore these questions...

If not, I would be interested in learning a little more before offering any suggestions. I am still not clear on the relationship between column C and column D. Are you saying that the value in column D (Material) somehow affects the quantity of stock in column A or C? Is that like the materials in column D (for a given row) is used to make more stock? If so, what is the relationship between column B and column D, since you have both of them in the same row?
 
Hello Sajan,

The relationship between column B and D is that how many piece has been delivered to the customer during the day. The basic problem is coming from my ERP system, that is not able to refresh the Ordered quantity (Column E) during a day (just one time, at 01:00 am) so I created a new column (Column F) to calculate the Open ordered quantity after the daily deliveries. This is the reason why I need the information about the daily deliveries of materials (Column D)... to know exactly the open ordered quantity after the daily outbound deliveries. When the value of Column C is turning to negative then means that the value of cell is missing to the Ordered quantity, so this is that quantity what must to produce.

Jake's answer was correct, so Jake please, receive my honourable mention!

Just... I would like to extend my first question with two additional things, I hope this is not a big problem, if it is conflicts with the rules of forum, please do not take into account, if not then:

#1 How does it looks like the formula if one PN only once in the list?
#2 How does it looks like the formula if there is a stock and nothing has been delivered?

I have uploaded the modified workbook.
 

Attachments

  • Sumif difficulties_Chandoo_org2.xlsx
    12 KB · Views: 3
Dear Villalobos

for your extension

=IFERROR(OFFSET($C$1,LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1,0)-OFFSET(E2,0,IF(OR(D2="c",D2="e"),0,1)),IF(OR(D2="A",D2="B",D2="C"),A2-B2,IF(D2="E",A2-E2,E2-A2)))

entered in C2 with ctrl+shift+enter fill down

I couldn't tell what is supposed to happen with a second value of D in column D, so I've assumed it will follow the same rules as the second A or B . If you want it to follow the same rules as a second C or E then the following is what you want.

=IFERROR(OFFSET($C$1,LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1,0)-OFFSET(E2,0,IF(OR(D2="c",D2="D",D2="e"),0,1)),IF(OR(D2="A",D2="B",D2="C"),A2-B2,IF(D2="E",A2-E2,E2-A2)))
 
Hello Jake,

First of all please, allow me to say thank you very much for your attention! Your formulae is working fine, but in my workbook where I would like to use this formulae there are 3 hundred different materials in column D. Which kind of argument should I have to repeat for that the formulae could be to handle all materials?
 
Dear Villalobos

It would all depend on what behaviour you would want the calculation to adopt for each of the different materials.

The last formula I wrote basically sets the behaviour of material A and B to be the default if it is not the first instance of that material, and the behaviour of material D to be the default if it is the first instance of that material.

However, this can all be modified however you would like it, but you'd've to explain the logic you want for the default behaviour and give all the special cases you want to behave differently.

To help you out I'll attempt to explain the formula in more detail

=IFERROR(OFFSET($C$1,LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1,0)-OFFSET(E2,0,IF(OR(D2="c",D2="e"),0,1)),IF(OR(D2="A",D2="B",D2="C"),A2-B2,IF(D2="E",A2-E2,E2-A2)))

So starting with the iferror statement this takes two parameters, the first a function it attempts to calculate and second what to do if that function evaluates to and error

the first function is

OFFSET($C$1,LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1,0)-OFFSET(E2,0,IF(OR(D2="c",D2="e"),0,1))

the second function is

IF(OR(D2="A",D2="B",D2="C"),A2-B2,IF(D2="E",A2-E2,E2-A2)).

So the first function (what the if error tries to calculate)

This is in two parts part 1 is

OFFSET($C$1,LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1,0)

partt 2 is

OFFSET(E2,0,IF(OR(D2="c",D2="e"),0,1))

both parts use the offset function with 3 parameters the first being a cell to start with, the second parameter is the number of rows to move down from that start cell and the third parameter the number columns to move right from the start cell and will return the value in that cell.

so the first part starts at c1 and moves 0 columns and the clever bit is the determination of the rows with the formula LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1 which works out the previous instance in column D with the same value as the value in (in this case D2). If there is no such value (i.e. it is the first instance of the material) then this throws up an error (hence the use of iferror).

The second part checks to see if the the material is C or E and if so it will use the value in column E, otherwise it uses the value in column F and this second part was subtracted from the first part


So lets go back to what the iferror function does if this part throws up an error (basically if it is the first instance of that material)
IF(OR(D2="A",D2="B",D2="C"),A2-B2,IF(D2="E",A2-E2,E2-A2))

So here we check to see if the material is A,B or C in which case it does column A - column B, if the material is not A, B or C then it checks to see if the material is E if it is then it does column A - column E otherwise it does column E- column A (which is what material D does).
 
Dear Jake,

Thank you the detailed explanation. Your formula is able to handle the all general cases what I want. Based on your explanation the second function is that what should have to modify If I would like extend the formulae for the all materials. But I don't know how to generalize that. May be I should have to create a Named Range (example: [MaterialList]) where I store the all materials and somehow integrate into the formulae or is it bad way?
 
You must log in or register to reply here.
Back
Top