# Date range

#### chrispy

##### Board Regular
I am trying to retrieve information from an existing spread sheet that meets certain criteria.
Column A is a date, Column B is Technicians name, Column C is the sales amount and Column D is number of calls ran that day.

I would like to retrieve the total sales for a particular technician within a specific date range. The beginning date range is in cell AA1, the ending date is in cell AB1, and the technician name is in cell AC1. I would also like to total the number of calls the specified tech ran in the same time period.

Any help on this would be greatly appreciated. Currently I am retrieving the information using filters on the columns but this is cumbersome.

Thanks!

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Use COUNTIFS. Its pretty intuitive to use.

I tried COUNTIFS and SUMIFS but I can not get the date range to work in the formula

Like this:

=COUNTIFS(A:A,">="&AA1,A:A,"<="&AB1)

Where A:A contains the dates. You will need to add in the technician name part to it.

I tried COUNTIFS and SUMIFS but I can not get the date range to work in the formula

If you want to hard-code the dates in the formula, try something like this:

Excel 2010
ABCDE
1DateNameValueCount
212/5/2015B12
31/1/2016B2
41/4/2016B3
51/6/2016C4
62/1/2016D5
Sheet1
Cell Formulas
RangeFormula
E2=COUNTIFS(A:A,">=1/1/2016",A:A,"<2/1/2016",B:B,"B")

Last edited:
1st are tour dates real dates, and not text looking like dates?
test with =isnumber(A2) with A2 being 1 of your dates. If you get FALSE, you do not have real dates and we will need to convert them.

Assuming you have real dates...
Untested
=SUMIFS(C:C,A:A,">="&\$AA\$1,A:A,"<="&\$AC\$1,B:B,\$AC\$1)

For the count, I think you should be able to just leave out the C:C range
Column A is a date, Column B is Technicians name, Column C is the sales amount and Column D is number of calls ran that day.
The beginning date range is in cell AA1, the ending date is in cell AB1, and the technician name is in cell AC1.

The genius that emanates from this message board never ceases to amaze me!

Thanks, that works perfectly.

Replies
5
Views
79
Replies
3
Views
642
Replies
18
Views
274
Replies
2
Views
148
Replies
0
Views
274

1,196,516
Messages
6,015,680
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back