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,
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"...my dates are merged every three columns "


"Any suggestions would be awesome "

Don't merge the cells.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
Under CTRL + 1 (Cell formatting), you can use "center across selection" -- instead of merging.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
My opinion, merged cells are inappropriate for spreadsheet modeling.

Get rid of the merged cells and do as suggested above.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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
Top