Forum Discussion
IF Between Two Dates, Perform Function
Hi all,
I have a very large dataset and I am trying to do something pretty basic. Within the data, I want to focus on cells with dates between two ranges. If the date is between the range, I want to perform
=SUMPRODUCT(IFERROR(--((Data!AY:AY-30)>Data!AX:AX),0)).
This is checking if the date in AY is more than 30 days past the date in AX. I want to break this down for every month, checking if AY occurred in that month and if it is more than 30 days greater than AX.
I can count every report sent in that month using
=COUNTIFS(Data!AY:AY,">="&EDATE(A4,0),Data!AY:AY,"<="&EDATE(A4,1)-1)
which works fine. I have tried
=IF(AND(Data!AY:AY>=(EDATE(A4,0)),Data!AY:AY<=(EDATE(A4,1)-1))),(SUMPRODUCT(IFERROR(--((Data!AY:AY-30)>Data!AX:AX),0)))
but no luck. I've attached a sample spreadsheet, removing all the confidential information. Not sure where to go from here. Any help would be appreciated!
2 Replies
- so if ay-ax >30 days, then what do you want to count? can u elaborate more, if ay-ax>30 you want to see how many reports in column ay with certain date? please explain more, if you can show one example please
chahine Sure no problem.
On the metrics worksheet, I want to look at all reports sent within that month in AY of the Data worksheet. From there, I want to check if it took over 30 days to submit the report compared to the report in AX.
In the data below, my IF(AND statement would find 2 reports sent in Jan 2022. Then it would apply my SUMPRODUCT function to determine if those reports were more than 30 days passed the report in AX. This would result in 1 since of the two reports sent in Jan 2022, only 1 was more than 30 days past the report sent in AX.
