SUMPRODUCT Formula Help?

[jimrosser]

Hi All,

Can anyone helpw with the following?
I have 3 different categories and need this same style formula in the 2nd and 3rd categories not to exceed the first nor be a negative number.
1st category max is 45 and is in cell C119
2nd category mas is 33 and is in cell C120
3rd Category max is 23 and is in cell C121

First formula
=IFERROR(IF(SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="P")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="t")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="a")))>$C$119,$C$119,(SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="P")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="t")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="a"))))),"0")

My attempt at 2nd formula
=IF((IF(SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="P")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="t")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="a")))-$C$119>$C$120,$C$120,(SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="P")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="t")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="a")))))-$C$119)>0,SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="P")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="t")))+SUMPRODUCT(($A$3:$A$108="ASG")*((GF$3:GF$108="a")))-$C$119,0)

[FDibbins]

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Richard Buttrey]

...in addition please add your Excel version and location to your personal details.

Knowing these often helps when answering queries, and in your particular case if you have Excel 2007 or later then SUMIFS() functions are usually preferred to the older SUMPRODUCT() which was often used as a work around.

[daddylonglegs]

Use of IFERROR implies that Excel 2007 or later is being used.

Your first formula takes the form

=IF(formula>value,value,formula)

which you could simplify to MIN(formula,value)

....and you can combine the SUMPRODUCTs into 1 SUM/COUNTIFS so the first formula can be shortened to

=MIN(SUM(COUNTIFS($A$3:$A$108,"ASG",GF$3:GF$108,{"p","a","t"})),$C$119)

and then that should make the second

=MEDIAN(SUM(COUNTIFS($A$3:$A$108,"ASG",GF$3:GF$108,{"p","a","t"}))-$C$119,$C$120,0)

...and third

=MEDIAN(SUM(COUNTIFS($A$3:$A$108,"ASG",GF$3:GF$108,{"p","a","t"}))-$C$119-$C$120,$C$121,0)