IF formula nested not working

[certain_death]

Hi Guys
Can anyone help please

I have this formula:

=IF(AND(EOMONTH($S23,0)=(EOMONTH(AW$2,0)),$O23="One-Off"),$AB23,IF(AND(EOMONTH(AW$2,0)>=(EOMONTH($S23,0)),EOMONTH(AW$2,0)<=(EOMONTH($T23,0)),($S23=DATE(YEAR(AW$2),MONTH(AW$2),1))),$AC23,IF(EOMONTH($S23,0)=EOMONTH(AW$2,0),$AB23/$U23*(EOMONTH(AW$2,0)-$S23),IF(EOMONTH($T23,0)=EOMONTH(AW$2,0),$AB23-SUM($AJ23:AV23),IF(AND(EOMONTH(AW$2,0)>=(EOMONTH($S23,0)),EOMONTH(AW$2,0)<=(EOMONTH($T23,0))),IF(EOMONTH($S23,0)=EOMONTH(AW$2,0),$AB23/$U23*(EOMONTH(AW$2,0)-$S23),IF(EOMONTH($T23,0)=EOMONTH(AW$2,0),$AB23-SUM($AJ23:AV23),$AC23)),0)))))

Basically I want to say this:

Where month column and cell text is "One-Off", pick up AB23,

=IF(AND(EOMONTH($S23,0)=(EOMONTH(AW$2,0)),$O23="One-Off"),$AB23,

But then I want it to say zero if false.

But then if it is not "One-Off" do this:

IF(AND(EOMONTH(AW$2,0)>=(EOMONTH($S23,0)),EOMONTH(AW$2,0)<=(EOMONTH($T23,0)),($S23=DATE(YEAR(AW$2),MONTH(AW$2),1))),$AC23,IF(EOMONTH($S23,0)=EOMONTH(AW$2,0),$AB23/$U23*(EOMONTH(AW$2,0)-$S23),IF(EOMONTH($T23,0)=EOMONTH(AW$2,0),$AB23-SUM($AJ23:AV23),IF(AND(EOMONTH(AW$2,0)>=(EOMONTH($S23,0)),EOMONTH(AW$2,0)<=(EOMONTH($T23,0))),IF(EOMONTH($S23,0)=EOMONTH(AW$2,0),$AB23/$U23*(EOMONTH(AW$2,0)-$S23),IF(EOMONTH($T23,0)=EOMONTH(AW$2,0),$AB23-SUM($AJ23:AV23),$AC23)),0)))))

I realise I have two false criterias and its probably my nested logic, but I would really appreciate some help from the experts on here.

Thanks
CD

[daffodil11]

There appears to be quite a bit of repeated logic in your formula, perhaps something better referenced via a table lookup with comparisons.

Can you attach an example of how you're applying the formula?

[certain_death]

Hi Daffodil

Thanks for replying

I just need to spread across dates in Cols AJ:BI (unless it is "One Off" in Col O where I want it posted in the matching month header coloumn from Col S only)

If not "One Off" spread across matching month header columns from Col S and Col T.

thanks for looking
CD

[daffodil11]

I'm not sure I quite understand what your formula isn't doing that you want it to.

I rewrote the formula inserting manual line breaks with Alt+Enter, and spaces to line up the expressions.
I also removed a number of superfluous parens. Functionally, this formula operates no different but it's considerably easier to troubleshoot and understand.

Please Login or Register  to view this content.

[friendlykashish]

Try countif formula!!!

[certain_death]

Hi both

Thanks for your help

I will try and work my way through it.

Anyone else who can help it is appreciated

Thanks
Mark