Forum Discussion
Possibility to have a more practical formula
Dear All, thank you in advance for your support.
I have this formula which, as you can see, refers to multiple sheets, is there a simpler way of how to get the results I need (Formula Below)
=IF(D5='1'!H2,SUMIF('1'!C23:C48,Payslip!N5,'1'!R23:R48),IF(D5='2'!H2,SUMIF('2'!C23:C48,Payslip!N5,'2'!R23:R48),IF(D5='3'!H2,SUMIF('3'!C23:C48,Payslip!N5,'3'!R23:R48),IF(D5='4'!H2,SUMIF('4'!C23:C48,Payslip!N5,'4'!R23:R48),IF(D5='5'!H2,SUMIF('5'!C23:C48,Payslip!N5,'5'!R23:R48),IF(D5='6'!H2,SUMIF('6'!C23:C48,Payslip!N5,'6'!R23:R48),IF(D5='7'!H2,SUMIF('7'!C23:C48,Payslip!N5,'7'!R23:R48),IF(D5='8'!H2,SUMIF('8'!C23:C48,Payslip!N5,'8'!R23:R48),IF(D5='9'!H2,SUMIF('9'!C23:C48,Payslip!N5,'9'!R23:R48),IF(D5='10'!H2,SUMIF('10'!C23:C48,Payslip!N5,'10'!R23:R48),IF(D5='11'!H2,SUMIF('11'!C23:C48,Payslip!N5,'11'!R23:R48))))))))))))
Thank You once Again
5 Replies
AlbertM1960How about this?:
=SUMIF(INDIRECT("'"&SWITCH(D5,‘1’!H2,1,‘2’!H2,2,‘3’!H2,3,‘4’!H2,4,‘5’!H2,5,‘6’!H2,6,‘7’!H2,7,‘8’!H2,8,‘9’!H2,9,‘10’!H2,10,‘11’!H2,11)&"'!C23:C48,Payslip!N5,'"&SWITCH(D5,‘1’!H2,1,‘2’!H2,2,‘3’!H2,3,‘4’!H2,4,‘5’!H2,5,‘6’!H2,6,‘7’!H2,7,‘8’!H2,8,‘9’!H2,9,‘10’!H2,10,‘11’!H2,11)&"'!R23:R48)"
Thank you so much for your answer Steve. Your formula is much more practical than what I used, but I am looking for something that will do the job even if I add more sheets. The sample formula I sent is only one of 6 formulas I am using, so if I add a sheet I will always have to modify the six formulas, in other words I need a formula that does not need the Sheet Names, if it is possible.
AlbertM1960 Since the SUMIF seems to repeat based except for the sheet reference, maybe considered consolidating all the tables to one final sourced table. That way the SUMIF can then be updated to SUMIFS, taking into the account the new variable (ie. the month). If you can attach a sample workbook, removing any sensitive data, I can create a sample of what I mean.
AlbertM1960 Create a range on the same sheet as the cell with the formula, with formulas referring to H2 on the sheets 1 to 11.
For example in F1 ='1'!H2, in F2 ='2'!H2 etc.
You can then use
=SUMIF(INDIRECT("'"&MATCH(D5,F1:F11,0)&"'!C23:C48"),Payslip!N5,INDIRECT("'"&MATCH(D5,F1:F11,0)&"'!R23:R48"))
