Good Evening everyone! Thank you for taking the time to read and possibly reply.
I need to update an IF formula (I didn't create it) and nest it with another IF formula, I have been looking and trying for a few hours and haven't made any progress. Because of the way the formula was created and my lack of experience I haven't been able to figure out.
I've included 2 pictures (1st. 'Report' | 2nd. 'Open Positions') in hope of explaining how this was all built and to explain the logic of what it's currently doing and what I need it to do.
=IF($C$318=$C$319,SUMPRODUCT(SUBTOTAL(3,OFFSET('Open Positions'!G2,ROW('Open Positions'!G2:G140)-ROW('Open Positions'!G2),0)),('Open Positions'!G2:G140="OPEN")+0,'Open Positions'!$N2:$N140),(SUMPRODUCT(('Open Positions'!$A2:$A140=$D$1191)*('Open Positions'!G2:G140="Open")+0,'Open Positions'!$N$2:$N$140)))
---------------------------------------------
This is a scheduler that is being created, all employees are categorized by a 'Cost Center'. If there a position that is open the cost center is filled in along with the potential schedule that position would carry and the number of hours for said slot (column J in 'Open Positions'). The formula finds the count of 'OPEN' for each row and multiplies by 'Hrs/Shift'. This takes in consideration if a Cost Center has been filtered out and thus only counts those selected. Currently the formula works perfectly.
I have been asked that IF columns [C- I 'Open positions'] are all 'OFF' BUT there is data in columns [K - Q 'Open positions'] have been filled in rather than the appropriate manner that the calculation still runs.
Again THANK YOU to everyone that took the time to at least glance at this.
-Zeb
I need to update an IF formula (I didn't create it) and nest it with another IF formula, I have been looking and trying for a few hours and haven't made any progress. Because of the way the formula was created and my lack of experience I haven't been able to figure out.
I've included 2 pictures (1st. 'Report' | 2nd. 'Open Positions') in hope of explaining how this was all built and to explain the logic of what it's currently doing and what I need it to do.
=IF($C$318=$C$319,SUMPRODUCT(SUBTOTAL(3,OFFSET('Open Positions'!G2,ROW('Open Positions'!G2:G140)-ROW('Open Positions'!G2),0)),('Open Positions'!G2:G140="OPEN")+0,'Open Positions'!$N2:$N140),(SUMPRODUCT(('Open Positions'!$A2:$A140=$D$1191)*('Open Positions'!G2:G140="Open")+0,'Open Positions'!$N$2:$N$140)))
---------------------------------------------
This is a scheduler that is being created, all employees are categorized by a 'Cost Center'. If there a position that is open the cost center is filled in along with the potential schedule that position would carry and the number of hours for said slot (column J in 'Open Positions'). The formula finds the count of 'OPEN' for each row and multiplies by 'Hrs/Shift'. This takes in consideration if a Cost Center has been filtered out and thus only counts those selected. Currently the formula works perfectly.
I have been asked that IF columns [C- I 'Open positions'] are all 'OFF' BUT there is data in columns [K - Q 'Open positions'] have been filled in rather than the appropriate manner that the calculation still runs.
Again THANK YOU to everyone that took the time to at least glance at this.
-Zeb