If(AND) Nested Function long and complicated. Looking for simpler way if there is

Hello everyone,

Right now I have forecasts in one row, dates in another and I would like to assess the impact of a change in approval date (date when sales will start) to the revenue stream (forecasted). However, I would like to split this imoact per fiscal year (which ends May 31st).

I used the following formula:

=SUMIFS(A1:Z1,A2:Z2,">="&S3,A2:Z2,"<="&U3,A2:Z2,IF(AND(D1<DATE(YEAR(D1),5,31),D2<DATE(YEAR(D1),5,31)),"",IF(AND(D1<DATE(YEAR(D1),5,31),D2>DATE(YEAR(D1),5,31)),"<="&DATE(YEAR(D1),5,31),IF(AND(D1>DATE(YEAR(D1),5,31),D2>DATE(YEAR(D1),5,31)),"<="&DATE(YEAR(D1)+1,5,31),"Error"))))

where

A1:Z1 are my forecasts
A2:Z2 are my dates
D1 is the first (initial) date
D2 is the second (modified) date

The formula works properly, however, I would like to know if there is a way to simplify it so that others can understand it more easily.

Thank you for any help you may give,

Coco

Hi Coco,

Your details regarding cell allocation don't make sense as the first date lies within the range of your forecasts and second date lies within the range of your dates (both would be the fourth entry in their respective rows).

Please post a copy of the workbook - it does make life a bit easier than trying to visualize your data in a matrix.

Dave