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.
Aladin
==========
Posted by Ian on August 13, 2001 4:20 AM
Upgrade to SR2!!!
Posted by Judy on August 26, 2001 2:02 PM
I did what you said, but no go!
I upgraded to SR-2 and I adjusted the formula.
I get a value error with the formula change, and all the info that is subject to transfer itself onto page 10, according to their date, is all going on one line (Jan-02) instead of the proper month. Ca I email this workbook to you?
