Count based on 2 cell ranges being within certain date ranges

[AlexJames]

Hey there!

I'm reviewing some customer data and trying to count the number of orders placed by customers within certain months based on the date they were acquired.

I've got the start and end dates for each month plotted out in Columns C and E in one sheet. In another sheet I've got customer IDs on each row, the number of orders they placed plotted as columns with the dates they placed them as values.

The only way I can think to get the data I want pulling through is by using an IF and COUNTIFS statement which looks as follows, but it's not working:

=IF(AND(Sheet1!B:B>=C2,Sheet1!B:B<=E2),COUNTIFS(Sheet1!B:BQ,">="&C2,Sheet1!B:BQ,"<="&E2))

Does anyone have any suggestion on formulas to get the data I'm trying to see?

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

You are familiar with your data and layout, but we are not. Your question would most likely be a lot clearer and elicit responses if you provided some small sample dummy data & the expected result(s) manually entered with XL2BB and explain again in relation to that sample.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.