COUNTIF and VLookup Help

[ECAshley]

Hello - I have designed a spreadsheet that seems to be a little cumbersome to gather data from. In the long and short of it, I'm trying to create a count for how many ads we posted, in which cities, during which quarters. Here is how the data is laid out:[TABLE="width: 688"]
<tbody>[TR]
[TD]Location
[/TD]
[TD]Quarter - Website A
[/TD]
[TD]Quarter - Website B
[/TD]
[TD]Website A
[/TD]
[TD]Website B
[/TD]
[/TR]
[TR]
[TD]Portland, OR
[/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[TD]1/1/13
[/TD]
[TD]4/6/13
[/TD]
[/TR]
[TR]
[TD]Yakima, WA
[/TD]
[TD]Q2
[/TD]
[TD]Q2
[/TD]
[TD]5/16/13
[/TD]
[TD]5/16/13
[/TD]
[/TR]
[TR]
[TD]Springfield, ME
[/TD]
[TD]Q1
[/TD]
[TD]Q3
[/TD]
[TD]1/16/13
[/TD]
[TD]6/25/13
[/TD]
[/TR]
[TR]
[TD]New York, NY
[/TD]
[TD]Q4
[/TD]
[TD]Q3
[/TD]
[TD]7/26/13
[/TD]
[TD]6/4/13
[/TD]
[/TR]
</tbody>[/TABLE]

I need to be able to count how many ads we posted (date denotes 1 ad), during each quarter, for each location.
<br>Thank you for any help you can provide.

 

[shyy]

How do you want the end result to look like?

I am thinking of using a SUMPRODUCT =SUMPRODUCT(--(B1:B25="Q1"),--(D1:D25=DATE(2013,1,1))) so it counts how many Q1's you have posted for the date 1/1/2013

 

[ECAshley]

I was hoping it would look something like this:[TABLE="width: 870"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 4"] Website A
[/TD]
[TD="colspan: 4"]Website B
[/TD]
[TD="colspan: 4"]Website C
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[TD]Q3
[/TD]
[TD]Q4
[/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[TD]Q3
[/TD]
[TD]Q4
[/TD]
[TD]Q1
[/TD]
[TD]Q2
[/TD]
[TD]Q3
[/TD]
[TD]Q4
[/TD]
[/TR]
[TR]
[TD]Portland, OR
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yakima, WA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Springfield, ME
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York, NY
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ArthriticPanda]

Something like this...?

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Location	Website A				Website B
2		Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
3	Portland, OR	1	0	0	0
4	Yakima, WA	0	1	0	0
5	Springfield, ME	1	0	0	0
6	New York, NY	0	0	0	1
7	Totals	2	1	0	1
Sheet1

Cell Formulas
Range	Formula
B3	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A3),--(Sheet2!$B$2:$B$5=Sheet1!$B$2),--(Sheet2!$D$2:$D$5>0))
B4	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A4),--(Sheet2!$B$2:$B$5=Sheet1!$B$2),--(Sheet2!$D$2:$D$5>0))
B5	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A5),--(Sheet2!$B$2:$B$5=Sheet1!$B$2),--(Sheet2!$D$2:$D$5>0))
B6	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A6),--(Sheet2!$B$2:$B$5=Sheet1!$B$2),--(Sheet2!$D$2:$D$5>0))
B7	=SUM(B3:B6)
C3	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A3),--(Sheet2!$B$2:$B$5=Sheet1!$C$2),--(Sheet2!$D$2:$D$5>0))
C4	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A4),--(Sheet2!$B$2:$B$5=Sheet1!$C$2),--(Sheet2!$D$2:$D$5>0))
C5	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A5),--(Sheet2!$B$2:$B$5=Sheet1!$C$2),--(Sheet2!$D$2:$D$5>0))
C6	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A6),--(Sheet2!$B$2:$B$5=Sheet1!$C$2),--(Sheet2!$D$2:$D$5>0))
C7	=SUM(C3:C6)
D3	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A3),--(Sheet2!$B$2:$B$5=Sheet1!$D$2),--(Sheet2!$D$2:$D$5>0))
D4	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A4),--(Sheet2!$B$2:$B$5=Sheet1!$D$2),--(Sheet2!$D$2:$D$5>0))
D5	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A5),--(Sheet2!$B$2:$B$5=Sheet1!$D$2),--(Sheet2!$D$2:$D$5>0))
D6	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A6),--(Sheet2!$B$2:$B$5=Sheet1!$D$2),--(Sheet2!$D$2:$D$5>0))
D7	=SUM(D3:D6)
E3	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A3),--(Sheet2!$B$2:$B$5=Sheet1!$E$2),--(Sheet2!$D$2:$D$5>0))
E4	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A4),--(Sheet2!$B$2:$B$5=Sheet1!$E$2),--(Sheet2!$D$2:$D$5>0))
E5	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A5),--(Sheet2!$B$2:$B$5=Sheet1!$E$2),--(Sheet2!$D$2:$D$5>0))
E6	=SUMPRODUCT(--(Sheet2!$A$2:$A$5=Sheet1!A6),--(Sheet2!$B$2:$B$5=Sheet1!$E$2),--(Sheet2!$D$2:$D$5>0))
E7	=SUM(E3:E6)

Regards,

AP