Sumproduct Formula Help

[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

[NBVC]

I am not sure I understand what you need.

Can you try explaining further?

[scottylad2]

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

[nawas]

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

[NBVC]

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:

Please Login or Register  to view this content.

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.

See attached.

[nawas]

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

[nawas]

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

[NBVC]

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:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Both confirmed with CTRL+SHIFT+ENTER not just ENTER