Sumproduct Project!

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
I have the following spreadsheet:
GOOGLE SEARCH_ALLOCATION.xls
ABCDEFGH
1February-07March-07
2SiteGroupQueriesGrossRevenueAllocableRevenueQueriesGrossRevenueAllocableRevenue
3
4kctv21,626.00$87.73$61.4126,275.00$66.12$46.28
5wpri17,434.00$66.46$46.5217,948.00$32.12$22.48
6wave16,775.00$96.86$67.8015,967.00$79.65$55.76
7KNDU-$-$-5,880.00$13.96$9.77
8WGCL-$-$-9,723.00$15.35$10.74
Summary


I am trying to create a sumproduct formula (or one that will accomplish my task)

I have the following Data list options below my data chart as seen above...
GOOGLE SEARCH_ALLOCATION.xls
ABCDE
26LOOKMEUP!!!!
27SiteGroupDescriptionDateANSWER!
28kctvGrossRevenue2/1/2007
Summary


I would like to be able to choose a site, choose a description ether Queries, Gross Revenue, or Allocable Revenue and the appropriate date

The above chart only covers feb but there are other Identical charts side by side so the next would be march 2007 "merged at the top every three columns" (unfortunaltely I cannot include this character limit for html post)

My problem is attempting to match the Date in row 1 first and then offsetting this by the desired choice of descriptions... since my dates are merged every three columns the sumproduct formula will lead me to the first column occurance of the correct date match from C1:T1 which by default is the Queries column...

Any suggestions on how I could get around this default if I preferred the Gross Revenue Column instead?

Any suggestions would be awesome

Thanks in advance,
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
"...my dates are merged every three columns "


"Any suggestions would be awesome "

Don't merge the cells.
 
Upvote 0
Under CTRL + 1 (Cell formatting), you can use "center across selection" -- instead of merging.
 
Upvote 0
reply

Thank you for your advice!

My main question of which I did a poor job of explaining...

Am I able to offset an array using sumproduct formula... for instance

Code:
=SUMPRODUCT(--(A$4:A$22=A28)*(C1:T1=D28+0),C4:T22)

Can I offset the column equivalent to )*(C1:T1=D28+0) to the right or left?

Is this possible without using an index or match case

Thanks agian! and sorry for any confussions
 
Upvote 0
I haven't looked at this closely but it would be easiest not to use the merged cells. It seems to me you would like to have the date at the top of every column. Could you insert a row, add the needed dates at the top of every column (three times for each month), then hide the row. Your visible labels could be for "presentation" and the formulas could reference the hidden row that has the date in every column.

Regards.
 
Upvote 0
My opinion, merged cells are inappropriate for spreadsheet modeling.

Get rid of the merged cells and do as suggested above.
 
Upvote 0
Sorry for the late reply...

Your right guy's, merged cells are a pain in the A@@ but I often find them useful for formating and appearance!

Thanks for the good advice,

CHOW!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top