Nested Ifs/Ands and some tough to figure out variables - Would appreciate your input

I have been putting together several multi-worksheet workbooks, with multiple formulas running between the sheets. I am over my head most of the time, but have been able to figure out most of what I need by trial and error, and searching the web.

I've hit a roadblock tonight trying to add some new functionality with (what I consider) a complicated formula, that has been working fine for quite some time.

This is the formula that is working fine...
=IF(AND(I$1>=Staff!$N12,OR(I$1 <= Staff!$O12,ISBLANK(Staff!$O12))),(Staff!$H12*Setup!$C$26)*H12,0)

It checks a start date (col N) and end date (col O) on the "staff" tab, and then checking those dates with cell i1...and if all is well it makes a simple calculation. Again, this works perfectly.

The new functionality that I am trying to add is to check another cell value (Staff!B10) for a text string...so something like If(Staff!B10="CAFS")...and if the result is true, it runs the above formula...and if the result is something other than the noted text string, I simply want to run the same formula with a small change (instead of Setup!$C$26 it will simply be Setup!$C$27).

I have tried several things including the two samples below...both of which simply return a "False" value.
=IF(Staff!B5<>"CAFS",(AND(I$1>=Staff!$N6,OR(I$1<=Staff!$O6,ISBLANK(Staff!$O6))))*(Staff!$H6*Setup!$C$26)*H6,IF(Staff!B5="CAFS",(AND(I$1>=Staff!$N6,OR(I$1<=Staff!$O6,ISBLANK(Staff!$O6))))*(Staff!$H6*Setup!$K$26)*H6))

=IF(Staff!B10="CAFS", then(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),(Staff!$H10*Setup!$K$26)*H10, IF(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),(Staff!$H10*Setup!$C$26)*H10,0)))

I apologize if I am doing a poor job of trying to explain my issue, but as I noted above, I'm over my head (and getting pretty frustrated). Any assistance would be greatly assisted.

Thanks,
Bill

Hmm try this and see if it work.

=IF(Staff!B10="CAFS", IF(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),(Staff!$H10*Setup!$K$26)*H10), IF(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),(Staff!$H10*Setup!$C$26)*H10,0))

Thanks you so much Finalazy! Your suggested formula worked perfectly! You saved me many more hours of frustration, so I really appreciate your quick response!

Originally Posted by westsidebill

Thanks you so much Finalazy! Your suggested formula worked perfectly! You saved me many more hours of frustration, so I really appreciate your quick response!

You are welcome. In fact you already got the formula just that you close the bracket in a wrong order. Glad it helps and please help to change the topic to solve =)