Non-Macro formula to perform a loop (Array)

O

OtterBA

New Member
Joined
May 2, 2011
Messages
13
I am trying to come up with a formula that will calculate mutliple arrays at once and ranges in one cell with out using a macro.
I have multiple tables of data that are used to calculate full spreadsheets where each table is for input purposes only. So far I have not had a problem of create a summary total for the different tables. The goal is for the end user to change a percentage and see how that changes affects the total with out having to go to the detailed spreadsheet. The input needs to be independent of the worksheets that depend on it for data.

The formula I have in my head will run a vlookup for each cell in a range and multiply each cell by the vlookup then return a sum of all the vlookups. Is this even possible with out a macro?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
OtterBA said:
I am trying to come up with a formula that will calculate mutliple arrays at once and ranges in one cell with out using a macro.
I have multiple tables of data that are used to calculate full spreadsheets where each table is for input purposes only. So far I have not had a problem of create a summary total for the different tables. The goal is for the end user to change a percentage and see how that changes affects the total with out having to go to the detailed spreadsheet. The input needs to be independent of the worksheets that depend on it for data.

The formula I have in my head will run a vlookup for each cell in a range and multiply each cell by the vlookup then return a sum of all the vlookups. Is this even possible with out a macro?
Click to expand...
Is this what you had in mind...

Book1
ABCD
1___Lookup
2___Values
3A7_A
4B97_C
5C65_D
6D49_F
7E23_Sum
8F62_183
Sheet2

Formula entered in D8:

=SUMPRODUCT(SUMIF(A3:A8,D3:D6,B3:B8))
 
Upvote 0
<TABLE style="WIDTH: 206pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=274><COLGROUP><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 132pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2592 height=17 width=176><TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=376><COLGROUP><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 132pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 height=17 width=176><TABLE style="WIDTH: 287pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=382><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 height=17 width=108><TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=284><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 25.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 height=34 width=108>Product</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 132pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 width=176>2012 Total Sales
Master</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Product 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590> $ 1,594.00 </TD></TR></TBODY></TABLE>






Channel
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 width=176>




Quarter
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 width=98>




Product 1
</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>ch1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Q1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>28.71%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>ch1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Q2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>21.29%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>ch1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Q3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>21.29%</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset; mso-outline-level: 1" height=15><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=15>ch1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Q4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>28.71%</TD></TR></TBODY></TABLE>


Rep
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 width=98>








Region
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 77pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 width=102>






Product 1
</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>4.50%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>19.10%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>4.40%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>8.80%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>5.10%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>5.00%</TD></TR></TBODY></TABLE>

Comp Schedule
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 width=98>













Product1
</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 0 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.20%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 100
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.30%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 200</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.35%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.40%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 400</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.50%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 500</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.60%</TD></TR></TBODY></TABLE>
Each one of these tables get multiplied for each rep. for 1 qtr so............... vlookup((rep1xquarter1xproduct1)/3,compsche,2)*(rep1xquarter1xproduct1)/3 but I want to sum all reps for all qtrs with out a long string of formula or macro. I could manually type this formula and sum it for all instances but I was thinking an array would work.
 
Upvote 0
<TABLE style="WIDTH: 206pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=274><COLGROUP><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 132pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2592 height=17 width=176><TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=376><COLGROUP><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 132pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 height=17 width=176><TABLE style="WIDTH: 287pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=382><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 height=17 width=108><TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=284><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 25.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 height=34 width=108>Product</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 132pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 width=176>2012 Total Sales
Master</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Product 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590> $ 1,594.00 </TD></TR></TBODY></TABLE>






Channel
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 132pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 width=176>




Quarter
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 width=98>




Product 1
</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>ch1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Q1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>28.71%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>ch1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Q2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>21.29%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>ch1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Q3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>21.29%</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset; mso-outline-level: 1" height=15><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 11.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=15>ch1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Q4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>28.71%</TD></TR></TBODY></TABLE>


Rep
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 width=98>








Region
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 77pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 width=102>






Product 1
</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>4.50%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>19.10%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>4.40%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>8.80%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>5.10%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Rep6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 align=right>5.00%</TD></TR></TBODY></TABLE>

Comp Schedule
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; WIDTH: 74pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2589 width=98>













Product1
</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 0 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.20%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 100
</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.30%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 200</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.35%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 300</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.40%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 400</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.50%</TD></TR><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2591 height=17> 500</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl2590 align=right>0.60%</TD></TR></TBODY></TABLE>
Each one of these tables get multiplied for each rep. for 1 qtr so............... vlookup((rep1xquarter1xproduct1)/3,compsche,2)*(rep1xquarter1xproduct1)/3 but I want to sum all reps for all qtrs with out a long string of formula or macro. I could manually type this formula and sum it for all instances but I was thinking an array would work.
 
Upvote 0
I actually have a formula that half works now.
{=sum(vlookup((B15:B25*B35*B45*B10)/3*1000000,a55:a60,2)*((B15:B25*B35*B45*B10)/3*1000000))} but it only half works. What I need is to lookup the first cell in the range B15:B25 then multiply and lookup and multiply by the value that was looked up and then continue once it has done this to all in the range B15:B25 then sum.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
233
Tetra201
Tetra201
V
  • Question Question
Formula to count % of goals in a total number of games
2
Replies
19
Views
393
Peter_SSs
Peter_SSs
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
211
Ludwig
L
L
  • Question Question
Creating macro for nested calculations within calculations
Replies
2
Views
148
LizN
L
E
  • Solved
Performance issue using a combination of dropdowns & lookups
Replies
9
Views
654
Akuini
Akuini

Forum statistics

Threads
1,224,602
Messages
6,179,843
Members
452,948
Latest member
UsmanAli786

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