• 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.

Sum based on more than two criteria

P

paradise

Member
Dear All,

Let me explain my problem.My company has different kinds of schemes on per unit of sales on different kinds of products on various dates.Now the problem is that,I have a data in which there is date,product and sales quantity in 'Required' sheet from 'B4:B9'. I want to have a sum of sales quantity of different kinds of products on each scheme criteria date range(B:E) X per unit of scheme(from 'master' sheet in column G).I have created another sheet named as 'master' in which I have set for e.g 4 kinds (shown in column 'C' defined as A,B,C,D)of scheme of different dates(Period is defined as start date as 'From' and end date as 'To' with different kinds of product.

Hence,you have to look following this :
1.Data range from 'master' sheet
2.Sum of all the product value based on different kinds of date range
3.Determine scheme qty of each product from master sheet of 'G' column.
4.Multiply sn.2 x 3
OR,you may do as you like

Kindly,help me in resolving the issue of this in my 'Required' sheet.

With Best Rgds,
paradise
 

Attachments

  • Scheme.xlsx
    13.3 KB · Views: 12
No,I have already tried,currently I am using ms office 2013.can you form a formula.
still solution is not found.

With best Rgds,
paradise
 
If I am getting you right, you want the total amount incurred from a specific product in a specific scheme.

You can use the below formula in cell C20:
=SUMIFS(master!$H$2:$H$21,master!$C$2:$C$21,$C$18,master!$B$2:$B$21,$B20)

Just amend the $C$18 to the requried scheme cell and $B20 to Required Product cell.

Hope this helps....:)
 
No,that formula you gave does not work.Why you ignore the data from B4:D9 from 'Required' Sheet.You should have to understand basic things before formulating a formula.Here first you should have to assign column 'C' data of 'master' sheet in 'Required'sheet as a reference in column E5 or G5 and then pull till E9 or G9.After this you have to do sum productwise C5:C9 of 'Required' sheet anywhere.Then at last again lookup column 'C' data reference which you gave in 'Required' sheet E5 or E9 to E9 or G9 and Product name which now you have summed up where you have kept and determine column G data from 'master' sheet.After this multiply each other then solution will be received.

There is no hard & Fast Rule that you go this way as I have said.Main aim is to get the solution.Total Scheme calculation

Still waiting for the reply.
paradise

PS.revised attached file
 

Attachments

  • Scheme_revised.xlsx
    14.2 KB · Views: 8
Hi paradise,

Try this in Required!C20 and apply to all cells:

Code: 
=SUMPRODUCT(($B20=master!$B$3:$B$21)*(Required!C$18=master!$C$3:$C$21)*(master!$G$3:$G$21))*SUMPRODUCT(($B20=$C$5:$C$9)*($D$5:$D$9))

Hope that helps!
 
No, the Date column (Column B) from B5:B9 you have not taken,hence the result displaying by your formula is wrong.I have shown result by manual workings highlighted with Blue as enclosed in excel workbook.You have to redefine your formula.
You can even develop a formula either using google spreadsheet,macro,or excel formula whichever you feel better.

With Best Rgds,

Encl.Excel file with your formula and manual working of exact answer
 

Attachments

  • Scheme_revised_chandoo.xlsx
    14.6 KB · Views: 12
@paradise

Hi

in your given example file with light blue color shaded which is link to date field but i am little bit confuse on this can you give us some more details the date filed is locked or what

as a blind shot i did this

Code: 
 =SUMIFS(master!$G:$G,master!$B:$B,$B29,master!$C:$C,C$28,master!$D:$D,"<="&$B5,master!$E:$E,">="&$B5)*SUMIFS($D$5:$D$9,$C$5:$C$9,$B29)

if any issued please let me know

Thanks

Patnaik
 
@Sqmpatnaik,
Hi,the formula did not worked.
As said by you,date file is neither locked nor other fields.It is already open you can do anything to arrive at target result.

With Best Rgds,
 
Hi, paradise!

Give a look at the uploaded file.

It uses a helper table in columns E5:H9 aside your input data being:
E5, index in worksheet master (array formula): =COINCIDIR(C5&B5;master!B$3:B$18&master!D$3: D$18;1) -----> in english: =MATCH(C5&B5,master!B$3:B$18&master!D$3: D$18,1)
F5, scheme: =INDICE(master!$A$3:$G$18;Required!$E5;7) -----> in english: =INDEX(master!$A$3:$G$18,Required!$E5,7)
G5, scheme type: =INDICE(master!$A$3:$G$18;Required!$E5;3) -----> in english: =INDEX(master!$A$3:$G$18,Required!$E5,3)
H5, sales qty * scheme: =D5*F5

The formula for the output section is:
C20: =SUMAR.SI.CONJUNTO($H$5:$H$9;$C$5:$C$9;$B20;$G$5:$G$9;C$18) -----> in english: =SUMIFS($H$5:$H$9,$C$5:$C$9,$B20,$G$5:$G$9,C$18)

E5: Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.

Copy E5:H5 down thru row 9.
Copy C20 across and down thru F23.

Just advise if any issue.

Regards!
 

Attachments

  • Sum based on more than two criteria - Scheme_revised (for paradise at chandoo.org).xlsx
    14.6 KB · Views: 5
Hi @paradise,
One approach to get the results you are after is to put the following formula in cell C20, and copy it through to F23:
=SUM(MMULT((FromDates<=TRANSPOSE(IF(DataProduct=$B20, DataDates)))*(ToDates>=TRANSPOSE(IF(DataProduct=$B20, DataDates)))*(ProductName=$B20)*SchemeValue*TRANSPOSE(DataSalesQty)*(SchemeType=C$18), ROW(DataProduct)^0))

with Ctrl + Shift + Enter

I got the following result:
Scheme Date Range A B C D
Name of Product Total scheme quantity in carton based on Sales Quantity(Carton) i.e per unit scheme X Total sales quantity of respective date range
Apple 5060 0 0 0
Mango 0 40320 0 0
Grapes 0 0 450288 0
Orange 0 0 0 63896


I have attached a modified workbook.

By the way, in your document, you made the following comment:
This is a sample case of one party ,however,I have almost more than 100 parties

Let us know if you need help scaling the above formula to additional data.

Cheers,
Sajan.
 

Attachments

  • Chandoo-paradise-Scheme_revised_chandoo.xlsx
    15 KB · Views: 12
@SirJB7
Thanks for the reply but he output you have shown is wrong.C21,C22.Refer the output as done by @Sajan above,I have done working there too along with solution.Without doing separate working in Column E,F,G,H formulate formula directly in C20 thru F23.

With Best Rgds,
 
Hi@Sajan

Can u do same thing with macro and google docs spreadsheet.
In addition to above,yes above mentioned was only of a single party.In reality I do have more than 100 parties i.e say around 500-600 parties whose range of rows are more than what stated over here where without formula it is not possible to do manually.Here I have seen your formula,but I have not yet verified by extending rows,if your formula is correct then perhaps it can be extended to further rows using same formula.If you think above formula you have furnished cannot be working on extending further rows,then kindly do revised your formula.

With Best Rgds,
:)
 
Hi @paradise,
Since the formula uses named range references, as long as the ranges are extended to include the additional data, the formula should work.

Someone else will need to help you with a VBA solution or something that works with Google Docs.

-Sajan.
 
Sajan said:
Someone else will need to help you with a VBA solution or something that works with Google Docs.
Click to expand...
@SirJB7
Hi, myself!
So long...
Google... what??? Is there anything more in Google that Search, Images, Play, YouTube, News, Gmail, Drive, Calendar, Translator, Maps, Earth, Groups & Hangouts? And if so, what in the hell for?
Regards!
 
@sajan
Can u explain me the breakdown of your formula.Becoz I have never used mmult simply I know is that it is the multiplications of two matrix .
Last but not least thanks for solving my problem.
 
Hello Paradise,

Based on Sajan's sheet, I got same output with SUMIFS,

=SUM(SUMIFS(SchemeValue,ProductName,$B20,ToDates,">="&IF(DataProduct=$B20,DataDates,100^100),SchemeType,C$18,FromDates,"<="&IF(DataProduct=$B20,DataDates,-1))*DataSalesQty)

Array Entered.
 
Hi@Haseeb A,

Thanks for your formula.Can you explain your formula along esp.of using 100^100 and -1,so that your formula might help me in some other cases.

With Best Rgds,
 
Hi@Sajan & Haseeb

One more condition is to be set in master sheet in 2nd last column is the figure of sales qty greater than or equal to(put any hypothetical value) then scheme would be given otherwise not.Kindly revise the formula.
I will be waiting for your reply
With Best regards
 
Hi@Sajan & Haseeb

One more condition is to be set in master sheet in 2nd last column i.e in Column F is the figure of sales qty greater than or equal to then scheme would be given otherwise not.I mean to say that if Column F meets the criteria of sales qty with that of Required Sheet Data Table sum of sales qty of respective period of respective product then Scheme would be given of Master Sheet in Column G,otherwise not.Here, you calculated scheme in all products sales as defined in master sheet earlier.But now a slight change is made is that if the product sales is >= Column F condition in master sheet,then scheme would be given otherwise not.​
You are kindly requested to revise the formula.​
I will be waiting for your reply​
With Best regards​
Encl.Excel workbook​
 

Attachments

  • Chandoo-paradise-Scheme_revised_chandoo_1.xlsx
    13.9 KB · Views: 7
Hi @paradise,
Did you try modifying either of the two formulas to extend them? If so, what was the result?
 
Hi Sajan
I have set one more criteria just a moment ago I have posted .Kindly revise your formula,I have enclosed workbook too.
 
Hi @paradise,
Since Haseeb's formula is more intuitive to understand, here is the modified version that supports the additional condition:
=SUM(SUMIFS(SchemeValue,ProductName,$B20,ToDates,">="&IF(DataProduct=$B20,DataDates),SchemeType,C$18,FromDates,"<="&IF(DataProduct=$B20,DataDates),colF,"<"&DataSalesQty)*DataSalesQty)

enter with Ctrl + Shift + Enter into cell C20

Cheers,
Sajan.
 
You must log in or register to reply here.
Back
Top