sumproduct help

[barbuella]

Hello Mr. Excel Board,

I had created a separate thread yesterday asking for help using VLOOKUP for multiple criteria, but it turns out that I may instead need to use SUMPRODUCT according to one of the users which replied to my other thread. Basically, what I would like to do is show the data from only the previous day for a particular line item for a report. I think I got it to work semi-correctly, but instead of a numerical value, I get "FALSE" which isn't the desired result. Screenshots are included for where I'd like the data to appear and the sheet where this data is coming from.

Thanks!

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	Yesterday's Date	5/19/2010
5
6
7
8	Sum of Total Imps
9	Opportunity Name	Campaign	Placement Description	AD Line Item No.	3rd Party Billing	Agency Adserver	Actual Start Date	Actual End Date	Ad Date	End Date	Ad Size	Ad Type	Quantity	OAS Scheduled Imps	Total	Yesterday's Delivery
10	abc video upfront_q2 2010	#N/A	Guaranteed Video Views	TO0003443_1	0	(blank)	5/18/2010	5/24/2010	5/18/2010	8/24/2010	300x250	#N/A	120,000	#N/A	-	#N/A
11	Air Force_Air Force 2010_Q2Q310	TO0003247_14-Airforce-728x90	Tagged Social media distribution --> Targeted media across Appssavvys most popular applications for men	TO0003247_14	0	(blank)	5/10/2010	10/31/2010	6/1/2010	6/14/2010	728x90	IAB	666,667	666,667	2	FALSE
12		TO0003247_15-Airforce-728x90	Where I've Been Social media distribution --> Targeted media across Appssavvys most popular applications for men	TO0003247_15	0	(blank)	5/10/2010	10/31/2010	5/10/2010	10/31/2010	728x90	IAB	333,333	333,334	18	#N/A
13		TO0003247_16-Airforce-728x90	Page Fad Social media distribution --> Targeted media across Appssavvys most popular applications for men	TO0003247_16	0	(blank)	5/10/2010	10/31/2010	5/10/2010	10/31/2010	728x90	IAB	333,333	333,334	12,228	#N/A
14		TO0003247_17-Airforce-300x250	Where I've Been Social media distribution --> Targeted media across Appssavvys most popular applications for men	TO0003247_17	0	(blank)	5/10/2010	10/31/2010	5/10/2010	10/31/2010	300x250	IAB	666,667	666,667	17,623	#N/A
scrumdetail

I need to pull data for the previous day from the "oas" tab which can be seen below. The date format I'm using, if needed to help with this, is 'mm/dd/yyyy'.

Excel Workbook
	A	B	C	D	E	F
1	Alt Ad ID	Date	Campaign	Imps*	Clicks	CTR
2	RE06-AppssavvyB	5/1/2010	RE06-AppssavvyB-728x90	0	2	0.00%
3	TO0002118_2	5/1/2010	TO0002118_2-PampersSwad-1x1	10,014	-	0.00%
4	TO0002119_1	5/1/2010	TO0002119_1-PampersSen-728x90	2	-	0.00%
5	TO0002119_2	5/1/2010	TO0002119_2-PampersSen-160x600	2,009	-	0.00%
6	TO0002120_1	5/1/2010	TO0002120_1-PampersEasy-1x1	4,936	-	0.00%
7	TO0002120_2	5/1/2010	TO0002120_2-PampersEasy-728x90	5	-	0.00%
8	TO0002120_3	5/1/2010	TO0002120_3-PampersEasy-160x600	1,167	-	0.00%
oas

 

[Aladin Akyurek]

It's hard to see how scrumdetail and oas is related. Try to create related (easily readable) samples with less detail and shorter (shortened) entries along with the desired results, with no formulas. And SumProduct is not a panacea to everything, quite the contrary.

 

[barbuella]

hi aladin,

the scrumdetail is a basically a pivot table which uses the data on oas as a resource. oas is combined of reporting which i compile daily. basically, on the scrum detail tab, we only want to see data from the previous day to see how each account is delivering. you'll have to forgive my excel ignorance as i'm not that great with the more complex formulas, but i was told on another thread i had created that sumproduct would be the way to go. if not, any advice/help you could give would be much appreciated.

i could report a smaller sample of the data and show you how this does relate if that would help?

thanks.

 

[barbuella]

here's a more condensed version of the screenshots so you can see how scrumdetail and oas relate to each other.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	Yesterday's Date	5/19/2010
5
6
7
8	Sum of Total Imps
9	Opportunity Name	Campaign	Placement Description	AD Line Item No.	3rd Party Billing	Agency Adserver	Actual Start Date	Actual End Date	Ad Date	End Date	Ad Size	Ad Type	Quantity	OAS Scheduled Imps	Total	Yesterday's Delivery
10	abc video upfront_q2 2010	#N/A	Guaranteed Video Views	TO0003443_1	0	(blank)	5/18/2010	5/24/2010	5/18/2010	8/24/2010	300x250	#N/A	120,000	#N/A	-	#N/A
11	Air Force_Air Force 2010_Q2Q310	TO0003247_14-Airforce-728x90	Tagged Social media distribution --> Targeted media across Appssavvys most popular applications for men	TO0003247_14	0	(blank)	5/10/2010	10/31/2010	6/1/2010	6/14/2010	728x90	IAB	666,667	666,667	2	FALSE
12		TO0003247_15-Airforce-728x90	Where I've Been Social media distribution --> Targeted media across Appssavvys most popular applications for men	TO0003247_15	0	(blank)	5/10/2010	10/31/2010	5/10/2010	10/31/2010	728x90	IAB	333,333	333,334	18	#N/A
13		TO0003247_16-Airforce-728x90	Page Fad Social media distribution --> Targeted media across Appssavvys most popular applications for men	TO0003247_16	0	(blank)	5/10/2010	10/31/2010	5/10/2010	10/31/2010	728x90	IAB	333,333	333,334	12,228	#N/A
14		TO0003247_17-Airforce-300x250	Where I've Been Social media distribution --> Targeted media across Appssavvys most popular applications for men	TO0003247_17	0	(blank)	5/10/2010	10/31/2010	5/10/2010	10/31/2010	300x250	IAB	666,667	666,667	17,623	#N/A
scrumdetail

here's the "oas" tab:

Excel Workbook
	A	B	C	D	E	F
1	Alt Ad ID	Date	Campaign	Imps*	Clicks	CTR
2892	TO0003247_15	5/18/2010	TO0003247_15-Airforce-728x90	1	-	0.00%
2893	TO0003247_16	5/18/2010	TO0003247_16-Airforce-728x90	1,804	1	0.06%
2894	TO0003247_17	5/18/2010	TO0003247_17-Airforce-300x250	3,751	9	0.24%
2895	TO0003247_19	5/18/2010	TO0003247_19-Airforce-300x250	1,970	-	0.00%
oas

 

[Aladin Akyurek]

barbuella

I'm not trying to be difficult...

Try to state which sheet is the source and which sheet is the destination.

Provide a sample from the source sheet (no formulas).

Provide a sample from the destination that shows the desired result(s), based on the source sheet, again with no formulas.

An example of what I mean:

Sheet1, A2:B7 (Source)

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>6</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>6</TD></TR></TBODY></TABLE>

Sheet2 (Destination)

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Subtotal</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>17</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2</TD></TR></TBODY></TABLE>

B2:B4 houses the desired results, based on Sheet1.

...

 

[barbuella]

Sorry about that, i've just been trying to work on this and i'm getting frustrated with my own lack of knowledge..

Sheet1, A2:D5 (source)


Sheet2, A11:P14 (destination) [sidenote: i want the results to appear in column P from the previous day]


apologies again and i truly do appreciate the help..

 

[barbuella]

sorry about the previous images, since i don't post regularly, i'm still trying to figure how to do some of this correctly on here.

Sheet1, A2:D5 (source)

Sheet2, A11:P14 (destination) [sidenote: i want the results to appear in column P from the previous day]

 

[barbuella]

hello,

i thought i'd check back to see if the new information i posted helped or not. if you could let me know if this formula or perhaps another would work, i'd truly, truly appreciate it.

thanks!

 

[Aladin Akyurek]

hello,

i thought i'd check back to see if the new information i posted helped or not. if you could let me know if this formula or perhaps another would work, i'd truly, truly appreciate it.

thanks!

An image cannot be copied from here and pasted into Excel...

Try to set borders around the cells and copy the area and paste it here using Internet Explorer.

 

[barbuella]

hi aladin,

here's the data with the borders broken out around it.

Sheet1, A2:D5 (source)

Sheet2, A11:P14 (destination) [sidenote: i want the results to appear in column P from the previous day]