Countifs with multiple criteria and data range

Hi all,

Been using your site for a while now to get answers to problems but cant seem to solve this one so need some advice!

So Im having some issues using a countIfs with a date range. The purpose is to count a total amount of individual ID's that visit us based on a date range. We want to see how many come back in on the same day, within 3 days and within 30 days.

So my forumla for the within one days is:

=IF(COUNTIFS('Returned'!A:A,'Overall Data'!B1,'Returned'!B:B,'Overall Data'!A1)>0,"Yes","No")

For within 3 days:

=IF(COUNTIFS('Returned'!A:A,'Overall Data'!B1,'Returned'!B:B,">=" &A1+2)>0,"Yes","No")

For within 30 days:

=IF(COUNTIFS('Returned'!A:A,'Overall Data'!B1,'Returned'!B:B,">=" &A1+29)>0,"Yes","No")

Where Returned A:A is the ID, Returned B:B is the date and Overall data A:A is Date and B:B is ID.

The issue I am receiving is that all the data for within 30 days is lower than the data for within 3, essentially data returned for within 30 days should be higher as they have returned within 3 therefore the should have also returned within 30. Another issue is the file is massive (around 500k lines) so complex and long line formula are difficult to run so it needs to be short an elegant, any help is highly appreciated!

If I understand correctly (it's difficult to envisage your requirements without seeing a workbook), I think you need to do it like this:

=IF(COUNTIFS('Returned'!A:A,'Overall Data'!B1,'Returned'!B:B,">=" &A1,'Returned'!B:B,"<=" &A1+29),"Yes","No")

and if the formula is in the Overall Data sheet then you can remove the sheet reference to shorten the formula, like this:

=IF(COUNTIFS('Returned'!A:A,B1,'Returned'!B:B,">=" &A1,'Returned'!B:B,"<=" &A1+29),"Yes","No")

Hope this helps.

Pete

Hey Pete,

Thanks for the reply, this is working a treat! Thanks so much!

I also meant to point out that you should do a similar thing with your formula to count up to 3 days - as it stands, your formula counts the number of entries where the date is greater than or equal to A1 + 2.

Hope this helps.

Pete