Help w/ complicated nested IF

[zebil]

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

 

[oldbrewer]

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.

can you rewrite this please (have been filled in rather than the appropriate manner) is not at all clear

 

[zebil]

Thank you for the reply. I'll attempt to describe this better. The creator of this spreadsheet intended the Open Positions Hours to be calculated by simply putting OPEN/OFF in columns C - I and have this multiply by the value in column J with one calculation for each day. C2 (OPEN) X J2 (8) = 8 hours. Now they want to be able to leave Column C - I as 'OFF' BUT if they paste a schedule in column K - Q that this be the trigger for the calculation and not simply rely on the 'OPEN/OFF'.

Currently: IF C2 = OPEN X J2 = 8 hours.
Future: IF C2 = OFF then look if K2 <>"" then X J2 = 8 hours.

The kicker about all of this is in the first picture IF C318 doesn't match C319 then the calculations I just spoke about will ONLY count what is in C317.


Again thank you so much for trying to help.