Formula/Solution for Summary Page

[Lil Donnie]

Hello,
I'm new to excel so my research has lead me to this site.
Love it! I've already learned so much but may be looking for more help.
I believe I am running 2007

I believe my ultimate goal is to have a summary sheet from the data I've put in from a different sheet although I've been having trouble finding the right function to use to populate the information.

I am looking for a formula that will count a specific amount of times a word or phrase occurs within a date range and if possible, a word or phrase from two different cells within a date range.

For Example:
"CarA" visited this track "x" amount of times in "January"

We have three cars that visit different tracks each month, I did have a separate sheet for each car for each month but I wanted to see if I could place all the data I have on one sheet and produce a summary for show.

Your help or suggestions is appreciated

Thanks
Lil Donnie

 

[VoG]

hh. Unclear whet you want. Try a screenshot http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[Lil Donnie]

Here is a small amount of data:

I would like to capture how many times each car/driver appeared at each track per month.

Thank you

 

[mgirvin]

Maybe:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Name	Track		Date	Date	Name	Track
2	1/1/2011	Car 1	Track 1		1/1/2011	1/31/2011	Car 1	Track 1
3	1/2/2011	Car 3	Track 3
4	1/3/2011	Car 2	Track 3		2003 or earlier	2
5	1/4/2011	Car 1	Track 5		2007 or later	2
6	1/5/2011	Car 2	Track 5
7	1/6/2011	Car 1	Track 1
8	2/1/2011	Car 2	Track 4
9	2/2/2011	Car 2	Track 4
10	2/3/2011	Car 1	Track 4
11	2/4/2011	Car 3	Track 1
12	2/5/2011	Car 1	Track 5
13	2/6/2011	Car 3	Track 1
...

Cell Formulas
Range	Formula
F2	=EOMONTH(E2,0)
F4	=SUMPRODUCT(--(A2:A13>=E2),--(A2:A13<=F2),--(B2:B13=G2),--(C2:C13=H2))
F5	=COUNTIFS(A2:A13,">="&E2,A2:A13,"<="&F2,B2:B13,G2,C2:C13,H2)

 

[mgirvin]

From post #2, with that data set, maybe try a PivotTable:

1) Click in data set
2) Alt + N + V + T + Enter (or other method to create PivotTable)
3) Drag Car/Driver field to row labels
4) Drag Track field to column label
5) Drag Track field to Values area
6) Drag Date to Row label
7) Right-click Date field in row label area and point to Group
8) Group by Month and Year, click OK
9) Drag Date Field to Report Filter Area
10) Then use the drop down arrow in Report Filter to show each month.

 

[rallcorn]

PivotTable similar to MGirvin, but looks like your sample:

Car 1st in Row Labels
Track 2nd in Row Labels

Car also in Values (using COUNT)

Date in Column Labels & grouped by month & Collapsed (have to do this manually, or follow MGirvin's steps 6, 7 & 8 but drag to COLUMN Labels instead of Report Filter)

Remove Grand Totals for Rows & Columns if you don't want these.

 

[mgirvin]

If you want formulas:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Car	Track		Date
2	1/1/2011	Car 1	Track 1		1/1/2011	Excel 2003 or earlier
3	1/2/2011	Car 3	Track 3			Car 1	Car 2	Car 3
4	1/3/2011	Car 2	Track 3		Track 1	2	0	0
5	1/4/2011	Car 1	Track 5		Track 2	0	0	0
6	1/5/2011	Car 2	Track 5		Track 3	0	1	1
7	1/6/2011	Car 1	Track 1		Track 4	0	0	0
8	2/1/2011	Car 2	Track 4		Track 5	1	1	0
9	2/2/2011	Car 2	Track 4
10	2/3/2011	Car 1	Track 4		Date
11	2/4/2011	Car 3	Track 1		1/1/2011	Excel 2007 or later
12	2/5/2011	Car 1	Track 5			Car 1	Car 2	Car 3
13	2/6/2011	Car 3	Track 1		Track 1	2	0	0
14					Track 2	0	0	0
15					Track 3	0	1	1
16					Track 4	0	0	0
17					Track 5	1	1	0
...

Formula in cell F4, then copied through table:

=SUMPRODUCT(--($A$2:$A$13>=$E$2),--($A$2:$A$13<=EOMONTH($E$2,0)),--($B$2:$B$13=F$3),--($C$2:$C$13=$E4))

if no EOMONTH:

=SUMPRODUCT(--($A$2:$A$13>=$E$2),--($A$2:$A$13<=DATE(YEAR($E$2),MONTH($E$2)+1,0)),--($B$2:$B$13=F$3),--($C$2:$C$13=$E4))

Formula in cell F13, then copied through table:

=COUNTIFS($A$2:$A$13,">="&$E$11,$A$2:$A$13,"<="&EOMONTH($E$11,0),$B$2:$B$13,F$12,$C$2:$C$13,$E13)

 

[Lil Donnie]

This is fantastic, everything listed above worked out great when I tried it.

Thank you very much for everyone's help!

 

[mgirvin]

Woo Hoo!!!