Index/match/Search/product sum?

K

Kangah

Board Regular
Joined
Feb 9, 2009
Messages
54
Hi guys, not quite sure where to begin and I have tried a number of ways but I just don't know what will work and have already lost hours and I don't think I am any closer!

Hopefully someone can at least tell me what sort of formula/combination I will need.

Essentially what I need to do is populate the "table" at the bottom (D12:F15) with the numbers (in cells C2:N10) based on the Portfolio code, matches the Month (In Cell K12) and the "PRODUCT" Type (Column O).

So for example, Cell E13 should be $72132.2615 because it is the cell that is...

- Portfolio HGF01
- Month is "SEPTEMBER"
- Product is "TOTAL DATA

Any idea of what formula would work or where to begin?

Thanks in advance.

Excel Workbook
CDEFGHIJKLMNOP
1JULYAUGUSTSEPTEMBEROCTOBERNOVEMBERDECEMBERJANUARYFEBRUARYMARCHAPRILMAYJUNEPRODUCTPortfolio
250492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total DataHGF01
350492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total DataHGF02
450492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total DataHGF03
550492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total OtherHGF01
650492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total OtherHGF02
723752.972927146.254733932.818432236.177528333.927146.2515269.7722056.3330539.5429351.8933932.8235629.46Total OtherHGF03
823752.972927146.254733932.818432236.177528333.927146.2515269.7722056.3330539.5429351.8933932.8235629.46Total NextHGF01
923752.972927146.254733932.818432236.177528333.927146.2515269.7722056.3330539.5429351.8933932.8235629.46Total NextHGF02
109625.531911000.607913750.759913063.221911481.8811000.616187.8428937.99412375.6811894.4113750.7614438.3Total NextHGF03
11
12PortfolioTotal DataTotal OtherTotal NextMonthSEPTEMBER
13HGF01
14HGF02
15HGF03
Sheet1
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Hi Kangah,

Try inserting in E13 the following formula and copy down and across.

Excel Workbook
E
1372132.2615
Sheet1
Cell Formulas
RangeFormula
E13=SUMPRODUCT(($P$2:$P$10=$D13)*($O$2:$O$10=E$12)*OFFSET($A$1,1,MATCH($K$12,$A$1:$N$1,0)-1,9))

Hope this helps.

Regards.
 
Upvote 0
Hi,

Please download solution from:
http://www.box.net/shared/axxtfl1qi3a86diy6jhv
I hope this is what you need.

Kangah said:
Hi guys, not quite sure where to begin and I have tried a number of ways but I just don't know what will work and have already lost hours and I don't think I am any closer!

Hopefully someone can at least tell me what sort of formula/combination I will need.

Essentially what I need to do is populate the "table" at the bottom (D12:F15) with the numbers (in cells C2:N10) based on the Portfolio code, matches the Month (In Cell K12) and the "PRODUCT" Type (Column O).

So for example, Cell E13 should be $72132.2615 because it is the cell that is...

- Portfolio HGF01
- Month is "SEPTEMBER"
- Product is "TOTAL DATA

Any idea of what formula would work or where to begin?

Thanks in advance.

Excel Workbook
CDEFGHIJKLMNOP
1JULYAUGUSTSEPTEMBEROCTOBERNOVEMBERDECEMBERJANUARYFEBRUARYMARCHAPRILMAYJUNEPRODUCTPortfolio
250492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total DataHGF01
350492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total DataHGF02
450492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total DataHGF03
550492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total OtherHGF01
650492.583157705.809272132.261568525.648460230.4457705.8132459.5246885.9764919.0462394.4172132.2675738.87Total OtherHGF02
723752.972927146.254733932.818432236.177528333.927146.2515269.7722056.3330539.5429351.8933932.8235629.46Total OtherHGF03
823752.972927146.254733932.818432236.177528333.927146.2515269.7722056.3330539.5429351.8933932.8235629.46Total NextHGF01
923752.972927146.254733932.818432236.177528333.927146.2515269.7722056.3330539.5429351.8933932.8235629.46Total NextHGF02
109625.531911000.607913750.759913063.221911481.8811000.616187.8428937.99412375.6811894.4113750.7614438.3Total NextHGF03
11
12PortfolioTotal DataTotal OtherTotal NextMonthSEPTEMBER
13HGF01
14HGF02
15HGF03
Sheet1
Click to expand...
 
Upvote 0
cgcamal said:
Hi Kangah,

Try inserting in E13 the following formula and copy down and across.

Excel Workbook
E
1372132.2615
Sheet1
Cell Formulas
RangeFormula
E13=SUMPRODUCT(($P$2:$P$10=$D13)*($O$2:$O$10=E$12)*OFFSET($A$1,1,MATCH($K$12,$A$1:$N$1,0)-1,9))

Hope this helps.

Regards.
Click to expand...

WOW, that is awesome. I would have spent days on that and not got anywhere close.

Any chance of a walk through of what some of the things mean so I can know how/when to use this little gem int he future?

I understand to a point the SUMPRODUCT and how it is telling the formula where/what to match but I have not done much with OFFSET before.

Thanks again for your response was amazing!

=SUMPRODUCT(($P$2:$P$10=$D13)*($O$2:$O$10=E$12)*OFFSET($A$1,1,MATCH($K$12,$A$1:$N$1,0)-1,9))
Click to expand...

And thanks also to tnazirov, I didn't think of a PIVOT table either which might have been a good fall back option but trying to have a template done up that is less interaction sot hat others can use it who are completely hopeless with Excel :)
 
Upvote 0
Hmmm Sorry cgcamal, just tried putting it into my real sheet (the one I posted was justa s simplified version as it is split across 2 worksheets and a bit messier, as well as without real names etc of course.

So I definitely need to understand how the formula works.

Your Formula
=SUMPRODUCT(($P$2:$P$10=$D13)*($O$2:$O$10=E$12)*OFFSET($A$1,1,MATCH($K$12,$A$1:$N$1,0)-1,9))
Click to expand...


My adapted formula
=SUMPRODUCT((Sheet3!$S$2:$S$223='Specialist Alignment'!A2)*(Sheet3!$P$2:$P$223='Specialist Alignment'!$C$1)*OFFSET($A$1,1,MATCH('Sales by AE'!$E$2,Sheet3!$C$2:$N$223,0)-1,9))
Click to expand...

Where I think the issue may be is the "OFFSET($A$1,1,"

What does the $A$1,1 refer to?

Thanks again for the on going help! Hopefully you can help.
 
Upvote 0
Hi Kangah,

You're welcome. Nice to know it works.

For the adaptation if the structure is the same as your sample I think this would work.

=SUMPRODUCT((Sheet3!$S$2:$S$223='Specialist Alignment'!$A2)*(Sheet3!$P$2:$P$223='Specialist Alignment'!C$1)*OFFSET(Sheet3!$A$1,1,MATCH('Sales by AE'!$E$2,Sheet3!$A$1:$N$1,0)-1,222))

If the formula is not working yet, try to upload a more representative sample of your real file, If there are 3 sheets involved show the layout the
data is in each sheet, only change the real/confidential data.

Offset() use is as follow:
Syntax--> OFFSET(Ref,RowOffset,ColOffset,Height,Width)

-You define the Ref you need, in this case I use A1, the most left top cell.
-The ranges must go from row 2 up to row 223, then I use RowOffset=1. It will work OFFSET($A$2,,MATCH()-1,222) either.
-The column that matches your requirement will be given by MATCH(), but the column of A1=1 and if MATCH() returns 10, the offset must be 10-1=9.
So, the ColOffSet=MATCH()-1.
-Since data must go from row 2 to 223, then the Height of the array I need will be of 222 rows, in your sample was only of 9 rows.
-Width is not written because the default is an array of 1 column.

Hope this helps.

Regards.
 
Last edited:
Upvote 0
Thanks for the reply.

I think I kind of get it, but it doesn't work for me so I guess not!

Here is a link to the full 2 pages so hopefully you can point out where I am going wrong.

The Page that I want to populate is "Specialist Alignment" (Cells C2:E76) and the data table is on sheet named "Targets".

https://www.sugarsync.com/pf/D6596876_7637136_830534

Thanks again!
 
Upvote 0
Hi Kangah,

I'm not sure if is missing the sheet from where you get the month in the sample you uploaded.

Anyway, considering the value of month for which you want to do the lookup is in M2 of sheet "Specialist Alignment",
try inserting in C2 and copy down and across the following formula.

Excel Workbook
C
213750.7599
Specialist Alignment
Cell Formulas
RangeFormula
C2=SUMPRODUCT((Targets!$S$2:$S$223='Specialist Alignment'!$A2)*(Targets!$P$2:$P$223='Specialist Alignment'!C$1)*OFFSET(Targets!$A$2,,MATCH('Specialist Alignment'!$M$2,Targets!$A$1:$N$1,0)-1,222))



Hope this helps.

Regards
 
Upvote 0
WOW, simply fantastic...

I did have an issue with the month I was referencing (sorry yes it was on a different worksheet in the book) and for some unknown reason it wasn't working, so I just typed "SEPTEMBER" over the cell and it worked, so redid the drop down selection box and it worked.

You are amazing :)
 
Upvote 0
Very kind words!;), thanks and you're welcome.

Nice I could help you and that finally works in your real file.

Best regards.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Shifting cell reference
Replies
5
Views
112
Cubist
Cubist
W
  • Solved
Index/Match Cannot The Find Column
2
Replies
11
Views
1K
Peter_SSs
Peter_SSs
C
  • Solved
index match excel formula to vba code
Replies
9
Views
358
DanteAmor
DanteAmor
D
  • Question Question
Tally that shifts cells daily.
Replies
3
Views
815
portews
P
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,613
Messages
6,179,903
Members
452,948
Latest member
Dupuhini

Share this page

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