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:

Location	Quarter - Website A	Quarter - Website B	Website A	Website B
Portland, OR	Q1	Q2	1/1/13	4/6/13
Yakima, WA	Q2	Q2	5/16/13	5/16/13
Springfield, ME	Q1	Q3	1/16/13	6/25/13
New York, NY	Q4	Q3	7/26/13	6/4/13

<tbody>
</tbody>

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:

Website A

Website B

Website C

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Portland, OR

Yakima, WA

Springfield, ME

New York, NY

<tbody>
</tbody>

 

[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