Hi All,
I'm stuck with Sumproduct formula where i'm looking input with multiple criteria within the first and second array. Attached the sample worksheet for your kind review.
Many Thanks
Nawas
Hi All,
I'm stuck with Sumproduct formula where i'm looking input with multiple criteria within the first and second array. Attached the sample worksheet for your kind review.
Many Thanks
Nawas
I am not sure I understand what you need.
Can you try explaining further?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
From your sample, if you filter your table first by Dept the grade, Finance and M in your sample then the maximum salary there would not be what you#re indicating as the correct one. Is there another criteria you've not shown? If there's a further filter, say for Graduate then the answer would evaluate to 1150 and for Bcom it would be 1250. I get the same results using a max(IF array formula
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
Hi NBVC,
What i need is to get a max & min salary based on the "Department, Grade & Qualification" criterea, since i've different degrees which falls under one main. Eg: Graduate-Bcom, Masters-MBA. Therefore need to consider related degrees while getting the output.. the below is my formula =SUMPRODUCT(MAX((C3:C13=C19)*(D3:D13=D19)*(F19=F3:F13)*(E3:E13))), may be need to add IF condition to get if considering graduate include the related degrees and same like for masters.Hope you understand the concept.
Hi Scottylad,
You are right in that case it should get MAX value 1250 (which include Graduate & Bcom), sorry for that.
I really appreciate your help on this...
Many Thanks
Nawas
If there are going to be several possible Qualifications based on the Qualification picked, then you should make a table on the side with all the top level qualifications and their respective subordinates listed below.
So assuming this table is in J2:N4, then try these formulas:
For Max:
and for Min:Please Login or Register to view this content.
Note: Both formulas are array formulas and need to be confirmed with CTRL+SHIFT+ENTER not just ENTER to work.Please Login or Register to view this content.
See attached.
Many Thanks NBVC for your great support, its excellent solution. But when i applied it in my actual spreadsheet, formula is not taking for the Qualification's sabourdinate (Main- Secondary, Sabourdiante-Diploma). For your kind reference attached the sample worksheet.
Once again highly appreciating your great work...
Thanks
Apologize I forgot to mention attached worksheet's details. 1st Tab named: "List" is the total details of employees and 2nd Tab named:"Comparison" is the one which i applied the formulas.
Thanks
My solution was dependent on you have all the Possible Qualifications listed in the header row of the Comparison table (i.e in Row 12 of Comparison sheet.. and then list any subordinates that need to be included in the calculations for those header items below.
To work the way you are doing it the formula would change to the below... but note that if the Qualification in C3 is not listed in row 12 of Comparison, then it will only calculate for that specific Qualification...
On that note, try:
andPlease Login or Register to view this content.
Both confirmed with CTRL+SHIFT+ENTER not just ENTERPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks