# commission schedule formula won't work

Posted by Judy on August 11, 2001 11:53 AM

I am new, so bear with me in my terminology.
Am using Excel 97 SR-1
My workbook consist of 11 pages. Info is pulled from the first 9 sheets and placed on the 10th sheet based on the date and dollar amount. Here is my formula for the first 5 pages which should collect and compile as one entry column on sheet 10. Do you see any problem with this formula?

=SUM(IF(MONTH(Inter!\$D\$2:\$D\$40)=MONTH(A4),(Inter!\$H\$2:\$H\$40)))+SUM(IF(MONTH(Inter2!\$D\$2:\$D\$40)=MONTH(A4),(Inter2!\$H\$2:\$H\$40)))+SUM(IF(MONTH(Inter3!\$D\$2:\$D\$41)=MONTH(A4),(Inter3!\$H\$2:\$H\$41)))+SUM(IF(MONTH(Inter4!\$D\$2:\$D\$41)=MONTH(A4),(Inter4!\$H\$2:\$H\$41)))+SUM(IF(MONTH(Inter5!\$D\$2:\$D\$41)=MONTH(A4),(Inter5!\$H\$2:\$H\$41)))

Posted by Aladin Akyurek on August 11, 2001 12:43 PM

On the 10th sheet, use instead:

=SUMPRODUCT((MONTH(Inter!D2:D40)=MONTH(A4))*(Inter!H2:H40))+SUMPRODUCT((MONTH(Inter2!D2:D40)=MONTH(A4))*(Inter2!H2:H40))+...

If you get a 'formula too long error', put each SUMPRODUCT part in a cell of its own on the 10th sheet, then apply an ordinary SUM formula on those 9 cells.