Date range

[chrispy]

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.

This spreadsheet spans multiple years.

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!

 

[steve the fish]

Use COUNTIFS. Its pretty intuitive to use.

 

[chrispy]

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

 

[steve the fish]

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.

 

[63falcondude]

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
	A	B	C	D	E
1	Date	Name	Value		Count
2	12/5/2015	B	1		2
3	1/1/2016	B	2
4	1/4/2016	B	3
5	1/6/2016	C	4
6	2/1/2016	D	5
Sheet1

Cell Formulas
Range	Formula
E2	=COUNTIFS(A:A,">=1/1/2016",A:A,"<2/1/2016",B:B,"B")

 

[FDibbins]

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.

 

[chrispy]

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

Thanks, that works perfectly.