What am I missing in my formula? For some reason when cell I2=3, the total from cell BT38 is being multiplied 33 times.When cell I2=C, the answer is correct. (Adds up cells BS3 to BS35.)
=SUMPRODUCT(('Teacher 1'!$I$2="C")*('Teacher 1'!$K$3:$K$35=3)*('Teacher 1'!$BS$3:$BS$35)+('Teacher 1'!$I$2=3)*('Teacher 1'!$BT$38))
The way this formula is post to work is if cell "I2" has PK, K, 1, 2, 3, 4, 5, 6, 7 or 8, it will post the total from cell "BS38" and if cell "I2" has a "C", then it adds BS3 through BS35 that have a "3" in cell "K3" through "K35".
In this link, the answer in G41 should be 1947, not 64251.
http://i694.photobucket.com/albums/v...ps4b91c04c.jpg
In this link, the answer in G41 is correct. It adds column from BS3 to BS35.
http://i694.photobucket.com/albums/v...psf001cc1a.png
* I did hide some rows and columns to make the pictures smaller.
Here is the link to my sample spreadsheet
Bookmarks