How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions? Part-II

[e4excel]

How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions? Part-II

Dear Forum,

I am using the ingenious SUMPRODUCT to get the SUM through a Range of Columns and have done it succesfully courtesy the Excel Forum's help..
However, I feel like I have hit a wall now that the there is one more criteria which has come-up..
Finding the Stock received in particualr Months..

The example provided is a representation of the actual file as that cannot be uploaded due to sensitive nature of the data..

Basically, Countering Multiple Column Criterion Addition using SUMPRODUCT...what it means is I have columnar data and I need to add the Fruit Stock Received for a particular client in a specific month..

Now each Client has several Fruit Columns with each Fruit Date, but in actuality we are going to add only those Fruit stocks which are mentioned in the drop-down next to the Vendor Name.

I think the file would give a better picture..

Warm Regards
e4excel

[dilipandey]

Hi e4excel,

I am not able to get your query completely. what are you looking for and using what references? please highlight.. thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

[e4excel]

Hi e4excel,

I am not able to get your query completely. what are you looking for and using what references? please highlight.. thanks

Regards,
DILIPandey

If you observe the first solution in the cell E18 = 111 , the answer is derived from the 4 instances of Sachin in he entire data and only those fruit nos are considered which are mentioned in the Column C for Sachin...as shown in the depicted in the coloured cells..

Though there are nos mentioned under every fruit column the Summation would be done only for those which are mentioned in the Column C and therefore it will be as folows:

Sachin - Apples - 20
Sachin - Apples - 25
Sachin - Mangoes - 39
Sachin - Apples - 27

So the answer is 111 which is the addition for the above nos..

Now the second requirement is to get the Sum of only those Fruit NOs which meet the above requirement as well as which also fall in the given month...So for the same selection of Vendor i.e Sachin the answer would be 72..

Explanation:

Sachin - Apples - 29-May-11 - 20
Sachin - Apples - 19-May-11 - 25
Sachin - Apples - 16-May-11 - 27

The addition of the above nos is 72, theres nothing to do with Apples...

Hope this helps....!

Warm Regards
e4excel

[e4excel]

Sorry Forum,

I think I ahve made a slight mistake in the Uploaded File which I am trying to rectify so please find a new attachement soon which can help understand the problem better..

[e4excel]

PFA

Please change the Months DropDown to see the changes via Conditional Formatting

[e4excel]

The original solution was given to be my the great NBVC..

I am still in awe of this compact code which looks at Multiple Column Data and just adds the ones which are Matching and not all the columns..

Taking cue from this approach, I wanted to also get the Fruit Nos which are received in the selected Month and ofcourse matching the exisiting criteria..

http://www.excelforum.com/excel-work...riterions.html

Now we can select the Columns by using the Substitute function which looks at only those columns for processing however in the same data range trying to match an additional condition is challenging..
As the colums for checking and the columns to be added are different it becomes more difficult..

One more informative post
http://www.excelforum.com/excel-gene...n-for-row.html

[e4excel]

Dear Forum,

I am going sleepless thinking over this, as I have amanged to get both the answers seperately but when I combine it adds the Dates too..
I need to use the condition for the Falling Dates in such a way that it is used in the conditional area but a different Column Range should be considered while adding...

=SUMPRODUCT(--(Vendor=$C$14)*(SUBSTITUTE(Fruit," Fruit","")=FData),FruitData)  

This gives me the Fruit No for the Client "Sachin" for only the Fruits which are mentioned for Sachin in the COlumn C..

Now, I need to get the Fruit No for all the Fruits for Sachin as above + I also need the dates given for each Fruit to fall in a particular Month..

I can get the Count using the below formula:

=SUMPRODUCT(--((Vendor=$C$14)*(SUBSTITUTE(Fruit," Carton Date","")=FData)*(TEXT(FruitData,"mmm-yy")=TEXT($C$15,"mmm-yy"))))  

This gives me the correct count for the Fruits falling in the Range for the Specific Month for Sachin, however I need the SUM TOTAL..

=SUMPRODUCT(--(Vendor=$C$14)*((SUBSTITUTE(Fruit," Carton Date","")=FData)*(TEXT(FruitData,"mmm-yy")=TEXT($C$15,"mmm-yy"))),FruitData)  

This is an incorrect answer as it SUMS the Dates..

Is there a way of combining both conditions and get the SUM TOTAL for only the Fruit No Column though..

Warm Regards
e4excel

[NBVC]

Try:

Please Login or Register  to view this content.

[e4excel]

Try:

=SUMPRODUCT((Vendor=$C$14)*(SUBSTITUTE(OFFSET(Fruit,0,1,1,COUNTA(Fruit)-1)," Fruit","")=FData)*(TEXT(OFFSET(FruitData,0,0,ROWS(FruitData),COUNTA(Fruit)-1),"mmm-yy")=TEXT(C15,"mmm-yy")),OFFSET(FruitData,0,1,ROWS(FruitData),COUNTA(Fruit)-1))  

Dear NBVC,

Thats an understatement, I have actually not slept a wink and actually had given up...

And you made it look so easy and it seems cakewalk..

Thanks for helping and also for startling me again and again..

Would appreciate if you could please explain the working as I have never worked with an OFFSET in a SUMPRODUCT..as I will be utilizing this concept in my actual file which is similar..

P.S. Of late I read a lot about INDEX functions which are non-volatile and therefore consciously make an attempt to refrain from using OFFSET even for DROP_DOWN DEFINED NAMES..So just wanted to know that the OFFSET would be the only coercive funtion which can process it or even INDEX can be utilised here..

This is just for my knowledge as the Data would be maintained in different files and these results for the TOTALs in a different file..


Dear Forum,

I would like to make a special mention for the help provided to me by NBVC, as this has really helped me immensely not only in my Present Project but also showed me a completely different approach which I was not even close to, I would request you all to please take a look at the solution as it actually provides an answer in a compressed form which otherwise would have involved lot of SUM statements..

Warm REgards
e4excel

[Haseeb Avarakkan]

i think if you use one column right in the sum area should be fine.

eg: FruitData is in d3:O12, create another name SumRng with E3:P12 (note; Start one column after, Eend one column after actual data. leave last column P blank), then use SUMPRODUCT like,

=SUMPRODUCT((Vendor=C14)*(SUBSTITUTE(Fruit," Carton Date","")=FData)*(TEXT(FruitData,"mmm-yy")=TEXT(C15,"mmm-yy")),SumRng)

[e4excel]

Thanks Haseeb,

I tried that but I got a Value Error..

But what about the other condition to Sum the Ranges in the other Column, I mean that condition is not included in the code..!

When I started with this approach I thought that it will be simply adding 2 Substitute conditions in SUMPRODUCT..
But it was not so..!

This sure is a really complex code for me and I still trying to understand that, if there was an additional conditional in the same scenario where I had to SUM only those Fruits nos which are above 5 then, where would that go?

In my actual requirement I am trying to add payments from different Modes which are falling in a month as well as I want to sum only those payments which are above a certain MinAmt, SO my normal code without the Falling Month criteria would have been simply like this:

=SUMPRODUCT(--(Vendor=$C$14)*(SUBSTITUTE(Fruit," Fruit","")=FData)*(FruitData>MinQty),FruitData)  

Warm Regards
e4excel

[Haseeb Avarakkan]

E4, see the attached.

But what about the other condition to Sum the Ranges in the other Column...

I am sorry, which condition are you looking about?

[e4excel]

Hey Haseeb,

MY Bad, Morning Blues..I dont know why, but believe me It didnt work in the morning and I was really surprised as your solutions are always been very useful..
Maybe, it must not have got copied correctly..I am sorry for that..!

It works for the Fruit Ranges excluding Orange-1st Fruit, but I just realised that its still not giving correct answers if you have selected the option as Oranges then it ignores the Fruit Nos..

Unfortunately, Even NBVC's code is not giving the correct answer when the Orange-1st Fruit is selected..and the same month is selected it still does not count that..

Honestly, I have not understood NBVC's code but I think your's was a simple one to understand for me that Match from the original in the RIGHT(Actual) order, but for the SUMMING Range we just shift one column to the right.. Correct..!

Unfortunately happiness was shortlived as its ignoring the first column..

e4excel

[NBVC]

For me, with my formula, picking Yuvraj and say April 2011 works, it gives result of 35 (which is under Oranges columns).

Also, I received your PM shortly before I was going offline, I came up with that formula so that you don't have to create new named ranges as suggested by the other responder.

You are correct that OFFSET is volatile, but for one formula in the sheet, it shouldn't really be noticeable... but here is the version using INDEX instead and maintaining your original named ranges...

Please Login or Register  to view this content.

It is really not much more difficult, and not really necessary to create more ranges.... I think, just need to know how to manipulate...

[e4excel]

Dear NBVC,

I thought as much that you were going offline when I responded..
Thanks for the INDEX version for the same, now I have the two functions to understand..

Since you mentioned about the one-off Volatile formula , I just wanted to know,incase my Data SHeet is CLosed and this formula is on the reult sheet, it will not pose a problem incase if I were using the OFFSET version..

I am sorry even my conditional formatting is incomplete and incorrect as it did not colour the first column of Fruits..I did not realise it untill I changed some options to ORANGE and the Date to the Month of MAY..

So, is there a way out?

AS the INDEX version too ignores the ORange Column..and incase it its possible then I can have an additional small criteria based on the Fruit No where the Sum is only for those Fruit Nos which are greater than MinQty..

Thanks in Advance

[e4excel]

My Sincere Apologies to both of you NBVC and HAseeb..

I just realised that burning the Midnight Oil sometimes makes you cranky..

The spelling of Orange was simply Orange instead of Oranges as in the List and that was creating a NO-MATCH situation..

But please tell me how to still do the additional SUMRANGE > MINQTY ?

Thanks a lot both of you..

@ NBVC - Could we have more conditions in this including the Entire Range?

Warm REgards
e4excel

[Haseeb Avarakkan]

e4,

I have got 35 when select Yuvraj in Apr-11. probably you may have typed Oranges... I am not an expert, just sharing my thoughts.

[Haseeb Avarakkan]

Try this..

=SUMPRODUCT((Vendor=C14)*(SUBSTITUTE(Fruit," Carton Date","")=FData)*(TEXT(FruitData,"mmm-yy")=TEXT(C15,"mmm-yy"))*(SumRng>MinQty),SumRng)

[NBVC]

There is no sense in confusing you with different versions of the complex formula... if Haseeb wants to take over... then go for it....

[Haseeb Avarakkan]

@NBVC, my apologize.

I never thought to confuse OP with different version of formulas, just shared my small knowledge.

[NBVC]

No apology necessary..you can answer any thread you wish to. I just know that we are both approaching this a bit differently, and every time a question is asked of the OP we give 2 different versions. I am also here to help, not to confuse... so it is best that since you have the "better" approach with less functions, etc,... that you continue and I bow out.

[e4excel]

@NBVC,

I got it myself using your formula :

=SUMPRODUCT((Vendor=$C$14)*(SUBSTITUTE(INDEX(Fruit,1,2):INDEX(Fruit,1,COLUMNS(Fruit))," Fruit","")=FData)*(TEXT(INDEX(FruitData,1,1):INDEX(FruitData,ROWS(FruitData),COLUMNS(FruitData)-1),"mmm-yy")=TEXT(C15,"mmm-yy"))*(INDEX(FruitData,1,2):INDEX(FruitData,ROWS(FruitData),COLUMNS(FruitData))>20),INDEX(FruitData,1,2):INDEX(FruitData,ROWS(FruitData),COLUMNS(FruitData)))  

Thanks a lot, I was making mistakes in the brackets each time but kept on persevering..

@Haseeb, the entire morning I thought that there was some more work on the code but actually, I had made a mistake as there were 2 Different Speelings for "Orange" and "Oranges" which created the anomaly in both of your correct formulas and even in my own COnditional formatting which was correct though I am still not very happy using it as it starts one Column Behind..

@ NBVC - Could we have more conditions in this including the Entire Range?

The reason I am asking it is because you can actually FORESEE The formula like the back of your Hand whereas I will still take sometime for it to absorb really..

I will be using in my Payment File where unfortunately there are 2 Dates for the Cheque Mode, one is the "Cheque Receipt Date" and the other is the "Cheque Deposit Date", now when I need to check the payments recvd in a particular month it can so happen that the cheque was deposited late and that falls in the next month, I am still not exactly sure what the condition would be right now , but jsut wnat to know whether we can add one more condition? which again would bear the original SUBSTITUTE style with the word "Deposit Date" and "REceipt Date"..

I just want to know, not asking for the code at all..
Thanks a lot..for your help..

Warm Regards
e4excel