Date Ranges, Agents and Sums?

[jpennington2005]

I have a spreadsheet that I need to dispay the sales results for a certain agent from date to date.

Example:

I need to write a formula where the "data" is on a one tab and the "results" or on another tab. You enter the start date that you want to filter by in B6 of the Results Tab, The end date in D6. It needs to take that date range and filter the results on the "Data" Tab. In this example it would only be looking at rows 4-7. Let's say I want the results of B2 total sales for Jeff. Sales is in column C of the "Data" Tab, which in this example is the sum of C$ & C7. In "Results" D2 I need to know if the shoes came from the warehouse (boolean statement) in this case "0" is the result for D2 Same thing for C2 how many shoes did Jeff sell during this date range. (In the formula us D:D to look at the entire column. Do not use D1:D99999 use this format for all columns)

I have given 3 examples of the types of totals I need from a date range. Money, Boolean, & General numbers.

I am a newbie to this and not sure where exectly to look fr the right kind of formula to use. Any help would be greatly apreciated!

Jim


Formula for D2
=SUMPRODUCT(('Data'!B:B=Results!A2)*('Data'!D:D="Yes"))

"Results" Tab[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Agent
[/TD]
[TD="align: center"]Total Sales
[/TD]
[TD="align: center"]XL Shoes
[/TD]
[TD="align: center"]From Warehouse
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Jeff
[/TD]
[TD="align: center"]$85
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]John
[/TD]
[TD="align: center"]$45
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Joe
[/TD]
[TD="align: center"]$40
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Start Date
[/TD]
[TD="align: center"]2/1/13
[/TD]
[TD="align: center"]End Date
[/TD]
[TD="align: center"]2/28/13
[/TD]
[/TR]
</tbody>[/TABLE]


"Data" Tab
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Agent
[/TD]
[TD="align: center"]Sales
[/TD]
[TD="align: center"]XL Shoes
[/TD]
[TD="align: center"]From Warehouse
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]1/1/13
[/TD]
[TD="align: center"]Joe
[/TD]
[TD="align: center"]$50
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]Yes
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]1/4/13
[/TD]
[TD="align: center"]Jeff
[/TD]
[TD="align: center"]$75
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]Yes
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]2/3/13
[/TD]
[TD="align: center"]Jeff
[/TD]
[TD="align: center"]$35
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]No
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]2/4/13
[/TD]
[TD="align: center"]Joe
[/TD]
[TD="align: center"]$40
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]No
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]2/5/13
[/TD]
[TD="align: center"]John
[/TD]
[TD="align: center"]$45
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]Yes
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]2/25/13
[/TD]
[TD="align: center"]Jeff
[/TD]
[TD="align: center"]$50
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]No
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]3/18/13
[/TD]
[TD="align: center"]Joe
[/TD]
[TD="align: center"]$35
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]No
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

What version of Excel are you using?

M.

 

[jpennington2005]

Excel 2010

 

[Marcelo Branco]

Formula in B2
=SUMIFS(Data!$C:$C,Data!$B:$B,$A2,Data!$A:$A,">="&$B$6,Data!$A:$A,"<="&$D$6)
copy down

Formula in C2
=SUMIFS(Data!$D:$D,Data!$B:$B,$A2,Data!$A:$A,">="&$B$6,Data!$A:$A,"<="&$D$6)
copy down

Formula in D2
=IF(COUNTIFS(Data!$B:$B,$A2,Data!$A:$A,">="&$B$6,Data!$A:$A,"<="&$D$6,Data!$E:$E,"Yes"),1,"")
copy down

M.