Hi ,
Since you have asked what the error was in your formula , here goes my attempt.
First , let me explain the general methodology of trouble shooting.
The first rule of troubleshooting is Divide and Conquer.
If you have a big problem , see how you can reduce it to one or more smaller problems.
In any formula , if the range is G5:BC20928 , there are 2 ways you can reduce the size of the range viz. reduce the number of rows or reduce the number of columns ; thus one way you can reduce it to a smaller size is by making it G5:BC20. It is clear that you cannot reduce the number of columns.
The next step in reducing comes if you can check each component of a formula on its own.
Here , the complete formula is :
=IF($B11="Admissions",(SUMPRODUCT(ADMIT_DISCH_DATA_TBL)*('Admit-Disch Raw Data'!$B$5:$B$20928=$C$5&$C$6&$C11&$C$7)*(ADMIT_DISCH_YEAR_HEADER=I$10)*1),SUMPRODUCT((ADMIT_DISCH_DATA_TBL)*('Admit-Disch Raw Data'!$B$5:$B$20928=$C$5&$C$6&$C11&$C$8)*(ADMIT_DISCH_YEAR_HEADER=I$10)*1))
Since the outer IF is not so essential , we can see if it can be removed. B11 does have the text Admissions , which means the section if the check is false will never be executed.
Thus , instead of the above formula , we can reduce out contextual formula to :
=(SUMPRODUCT(ADMIT_DISCH_DATA_TBL)*('Admit-Disch Raw Data'!$B$5:$B$20928=$C$5&$C$6&$C11&$C$7)*(ADMIT_DISCH_YEAR_HEADER=I$10)*1)
When you test this formula , in a separate cell , it will be immediately clear , as to what the problem is - a missing parenthesis !
I have highlighted the problem portion.
To rectify this , just eliminate the parenthesis , which is coloured RED.
The corrected formula , which may or may not be the correct formula , will be :
=IF($B11="Admissions",(SUMPRODUCT(ADMIT_DISCH_DATA_TBL*('Admit-Disch Raw Data'!$B$5:$B$20928=$C$5&$C$6&$C11&$C$7)*(ADMIT_DISCH_YEAR_HEADER=I$10)*1)),SUMPRODUCT((ADMIT_DISCH_DATA_TBL*('Admit-Disch Raw Data'!$B$5:$B$20928=$C$5&$C$6&$C11&$C$8)*(ADMIT_DISCH_YEAR_HEADER=I$10)*1)))
Narayan
