# How to multiple different ranges of cells in an automated way?

#### douglascaixeta

##### New Member
Hi,

I have an excel sheet as showed below, and I would like to multiple the number of column 2, but the range is different all the time.

JAN 11
FEV 63
MAR 87
ABR 89
MAI 56
JUN 4
JUL 78
AGO 56
SET 45
OUT 22
NOV 1,2
DEZ 1,4

For example:
One event occur on Jan and finish on Mar. So, I inform the excel this 2 months and he should multiple 11, 63 and 87.

This event could be whatever, staring on Abr and finishing on Set. But never crossing, like starting on Nov and finishing on Jan. Because in the real database there is also the year variable, but is not important here. So I a bit easier. But how can a do it?

I think on using VLOOKUP. Then I have the first value and the last one. But how can I tell excel to multiple the range?
The ideal will be a VLOOKUP that returns the ADDRESS of the cell, not the value. But I didn't see this possibility.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
The PRODUCT function used with a range as argument (PRODUCT(A1:A12)) will multiply only the numbers found in the range - text values or empty cells will be ignored, so if it's possible to approximate the maximum length of the range you want to calculate, you may place the formula in a cell on, let's say, row 101 and multiply all the 100 numbers above (they might be 100, or 50 or only 3, the formula will return an accurate result).

I wonder if "multiple the number of column 2" should be interpreted as a SUM? If so, maybe this is what you're after:
Book2
ABCDE
1JAN11Start:JAN
2FEV63End:MAR
3MAR87Multiple:161
4ABR89
5MAI56
6JUN4
7JUL78
8AGO56
9SET45
10OUT22
11NOV12
12DEZ14
Sheet1

The formula in cell E3 looks for the month names in cells E1 and E2, then adds together the numbers for every month inside that range of months. Here's that formula:

=SUM(OFFSET(B1,MATCH(E1,A1:A12,FALSE)-1,0,MATCH(E2,A1:A12,FALSE)-MATCH(E1,A1:A12,FALSE)+1,1))

Hope this helps. If not, reply back and we will try to get it right for you.

Hi gecs,

I don't understand your solution. The range of the cells will change. The real database is huge. I can't select with PRODUCT function all the time. Is a lot of products and they have different ranges. If there is a VLOOKUP for ADDRESS I could use the result in a PRODUCT function.

I can also inform the first month and the number of months ahead. For example ABR and 4. So excel will multiple ABR, MAI, JUN and JUL.

Last edited:
If the search is ABR, MAI, JUN and JUL, those four numbers in your example data set are 89, 56, 4, and 78. What should the answer be:
A) 89 x 56 x 4 x 78 = 1,555,008
B) 89 + 56 + 4 + 78 = 227

Ok, I understood you want to multiply the values in a range of a variable length. Now, also reading gardnertoo's post , I realise his intuition is correct ("multiple" should be interpreted as SUM). Following his solution will lead you to the desired result.

[SOLVED] Re: How to multiple different ranges of cells in an automated way?

Thank you very much gardnertoo! It works perfectly.

I took some time to do the checks and it works.

I will put the file here, so somebody can use the idea as well.

http://rapidshare.com/files/211926098/IPCA_Inflation_Brazil.xls.html

The idea is to calculate the return of the investments (ROI) discounting the inflation. So I put 2 parameters, Year and Month of start and end of the investment. The formulation will search for the numbers on the database and multiply (PRODUCT) all the values and return the percentage.

The worksheet is just an example and is with inflation rates of Brazil, which is useful for me, and I will connect with other sheets, where I control the investments.

Thank you very much.

I have another problem now, with a similar database. I would like to do the same thing, multiple a range of values, giving 2 parameters, the date.
For example: here is the table (only 2 months)

****** http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"> <title></title> ****** name="GENERATOR" content="BrOffice.org 3.0 (Linux)"> <style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Arial"; font-size:x-small } --> </style> <table border="0" cellspacing="0" cols="2" rules="none" frame="void"> <colgroup><col width="86"><col width="86"></colgroup> <tbody> <tr> <td align="left" width="86" height="16">Data</td> <td align="left" width="86">Value</td> </tr> <tr> <td sdval="38718" sdnum="1046;0;DD/MM/AA" align="right" height="16">01/01/06</td> <td sdval="0,7338" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38719" sdnum="1046;0;DD/MM/AA" align="right" height="16">02/01/06</td> <td sdval="0,7626" sdnum="1046;" align="right">0,76</td> </tr> <tr> <td sdval="38720" sdnum="1046;0;DD/MM/AA" align="right" height="16">03/01/06</td> <td sdval="0,7577" sdnum="1046;" align="right">0,76</td> </tr> <tr> <td sdval="38721" sdnum="1046;0;DD/MM/AA" align="right" height="16">04/01/06</td> <td sdval="0,7609" sdnum="1046;" align="right">0,76</td> </tr> <tr> <td sdval="38722" sdnum="1046;0;DD/MM/AA" align="right" height="16">05/01/06</td> <td sdval="0,7256" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38723" sdnum="1046;0;DD/MM/AA" align="right" height="16">06/01/06</td> <td sdval="0,6919" sdnum="1046;" align="right">0,69</td> </tr> <tr> <td sdval="38724" sdnum="1046;0;DD/MM/AA" align="right" height="16">07/01/06</td> <td sdval="0,6916" sdnum="1046;" align="right">0,69</td> </tr> <tr> <td sdval="38725" sdnum="1046;0;DD/MM/AA" align="right" height="16">08/01/06</td> <td sdval="0,7252" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38726" sdnum="1046;0;DD/MM/AA" align="right" height="16">09/01/06</td> <td sdval="0,7585" sdnum="1046;" align="right">0,76</td> </tr> <tr> <td sdval="38727" sdnum="1046;0;DD/MM/AA" align="right" height="16">10/01/06</td> <td sdval="0,7545" sdnum="1046;" align="right">0,75</td> </tr> <tr> <td sdval="38728" sdnum="1046;0;DD/MM/AA" align="right" height="16">11/01/06</td> <td sdval="0,7382" sdnum="1046;" align="right">0,74</td> </tr> <tr> <td sdval="38729" sdnum="1046;0;DD/MM/AA" align="right" height="16">12/01/06</td> <td sdval="0,7191" sdnum="1046;" align="right">0,72</td> </tr> <tr> <td sdval="38730" sdnum="1046;0;DD/MM/AA" align="right" height="16">13/01/06</td> <td sdval="0,6721" sdnum="1046;" align="right">0,67</td> </tr> <tr> <td sdval="38731" sdnum="1046;0;DD/MM/AA" align="right" height="16">14/01/06</td> <td sdval="0,6799" sdnum="1046;" align="right">0,68</td> </tr> <tr> <td sdval="38732" sdnum="1046;0;DD/MM/AA" align="right" height="16">15/01/06</td> <td sdval="0,7124" sdnum="1046;" align="right">0,71</td> </tr> <tr> <td sdval="38733" sdnum="1046;0;DD/MM/AA" align="right" height="16">16/01/06</td> <td sdval="0,7536" sdnum="1046;" align="right">0,75</td> </tr> <tr> <td sdval="38734" sdnum="1046;0;DD/MM/AA" align="right" height="16">17/01/06</td> <td sdval="0,7455" sdnum="1046;" align="right">0,75</td> </tr> <tr> <td sdval="38735" sdnum="1046;0;DD/MM/AA" align="right" height="16">18/01/06</td> <td sdval="0,7407" sdnum="1046;" align="right">0,74</td> </tr> <tr> <td sdval="38736" sdnum="1046;0;DD/MM/AA" align="right" height="16">19/01/06</td> <td sdval="0,7098" sdnum="1046;" align="right">0,71</td> </tr> <tr> <td sdval="38737" sdnum="1046;0;DD/MM/AA" align="right" height="16">20/01/06</td> <td sdval="0,679" sdnum="1046;" align="right">0,68</td> </tr> <tr> <td sdval="38738" sdnum="1046;0;DD/MM/AA" align="right" height="16">21/01/06</td> <td sdval="0,6684" sdnum="1046;" align="right">0,67</td> </tr> <tr> <td sdval="38739" sdnum="1046;0;DD/MM/AA" align="right" height="16">22/01/06</td> <td sdval="0,7" sdnum="1046;" align="right">0,7</td> </tr> <tr> <td sdval="38740" sdnum="1046;0;DD/MM/AA" align="right" height="16">23/01/06</td> <td sdval="0,73" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38741" sdnum="1046;0;DD/MM/AA" align="right" height="16">24/01/06</td> <td sdval="0,7471" sdnum="1046;" align="right">0,75</td> </tr> <tr> <td sdval="38742" sdnum="1046;0;DD/MM/AA" align="right" height="16">25/01/06</td> <td sdval="0,7346" sdnum="1046;" align="right">0,73</td> </tr> <tr> <td sdval="38743" sdnum="1046;0;DD/MM/AA" align="right" height="16">26/01/06</td> <td sdval="0,6938" sdnum="1046;" align="right">0,69</td> </tr> <tr> <td sdval="38744" sdnum="1046;0;DD/MM/AA" align="right" height="16">27/01/06</td> <td sdval="0,677" sdnum="1046;" align="right">0,68</td> </tr> <tr> <td sdval="38745" sdnum="1046;0;DD/MM/AA" align="right" height="16">28/01/06</td> <td sdval="0,6389" sdnum="1046;" align="right">0,64</td> </tr> <tr> <td sdval="38749" sdnum="1046;0;DD/MM/AA" align="right" height="16">01/02/06</td> <td sdval="0,5729" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38750" sdnum="1046;0;DD/MM/AA" align="right" height="16">02/02/06</td> <td sdval="0,5737" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38751" sdnum="1046;0;DD/MM/AA" align="right" height="16">03/02/06</td> <td sdval="0,5793" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38752" sdnum="1046;0;DD/MM/AA" align="right" height="16">04/02/06</td> <td sdval="0,5773" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38753" sdnum="1046;0;DD/MM/AA" align="right" height="16">05/02/06</td> <td sdval="0,5773" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38754" sdnum="1046;0;DD/MM/AA" align="right" height="16">06/02/06</td> <td sdval="0,5753" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38755" sdnum="1046;0;DD/MM/AA" align="right" height="16">07/02/06</td> <td sdval="0,572" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38756" sdnum="1046;0;DD/MM/AA" align="right" height="16">08/02/06</td> <td sdval="0,5771" sdnum="1046;" align="right">0,58</td> </tr> <tr> <td sdval="38757" sdnum="1046;0;DD/MM/AA" align="right" height="16">09/02/06</td> <td sdval="0,5708" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38758" sdnum="1046;0;DD/MM/AA" align="right" height="16">10/02/06</td> <td sdval="0,5613" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38759" sdnum="1046;0;DD/MM/AA" align="right" height="16">11/02/06</td> <td sdval="0,5588" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38760" sdnum="1046;0;DD/MM/AA" align="right" height="16">12/02/06</td> <td sdval="0,5588" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38761" sdnum="1046;0;DD/MM/AA" align="right" height="16">13/02/06</td> <td sdval="0,5662" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38762" sdnum="1046;0;DD/MM/AA" align="right" height="16">14/02/06</td> <td sdval="0,5538" sdnum="1046;" align="right">0,55</td> </tr> <tr> <td sdval="38763" sdnum="1046;0;DD/MM/AA" align="right" height="16">15/02/06</td> <td sdval="0,5643" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38764" sdnum="1046;0;DD/MM/AA" align="right" height="16">16/02/06</td> <td sdval="0,5616" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38765" sdnum="1046;0;DD/MM/AA" align="right" height="16">17/02/06</td> <td sdval="0,5679" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38766" sdnum="1046;0;DD/MM/AA" align="right" height="16">18/02/06</td> <td sdval="0,5695" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38767" sdnum="1046;0;DD/MM/AA" align="right" height="16">19/02/06</td> <td sdval="0,5695" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38768" sdnum="1046;0;DD/MM/AA" align="right" height="16">20/02/06</td> <td sdval="0,5713" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38769" sdnum="1046;0;DD/MM/AA" align="right" height="16">21/02/06</td> <td sdval="0,5677" sdnum="1046;" align="right">0,57</td> </tr> <tr> <td sdval="38770" sdnum="1046;0;DD/MM/AA" align="right" height="16">22/02/06</td> <td sdval="0,5514" sdnum="1046;" align="right">0,55</td> </tr> <tr> <td sdval="38771" sdnum="1046;0;DD/MM/AA" align="right" height="16">23/02/06</td> <td sdval="0,5585" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38772" sdnum="1046;0;DD/MM/AA" align="right" height="16">24/02/06</td> <td sdval="0,5619" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38773" sdnum="1046;0;DD/MM/AA" align="right" height="16">25/02/06</td> <td sdval="0,5571" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38774" sdnum="1046;0;DD/MM/AA" align="right" height="16">26/02/06</td> <td sdval="0,5571" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38775" sdnum="1046;0;DD/MM/AA" align="right" height="16">27/02/06</td> <td sdval="0,5571" sdnum="1046;" align="right">0,56</td> </tr> <tr> <td sdval="38776" sdnum="1046;0;DD/MM/AA" align="right" height="16">28/02/06</td> <td sdval="0,5865" sdnum="1046;" align="right">0,59</td> </tr> </tbody> </table>
For each month, there is 28 days and 28 respective values.

I would give 2 dates: 03/01/06 and the end month 02 (Fev), because the day will always be the same of the first date. The year can change.

With those 2 parameters the excel should get the value from 03/01/06 (0,76) and multiple with the value same day and the month that I informed (0,58).

With 2 months is easy, but I have thousands of months. 2006 until 2009.

So, is the same case of my first post, but now, instead of using all the values of the range, I have to use only the value of each month in the range.

Replies
5
Views
621
Replies
2
Views
141
Replies
5
Views
167
Replies
4
Views
139
Replies
15
Views
490

1,219,828
Messages
6,150,470
Members
450,966
Latest member
Yali

### 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.

### Which adblocker are you using?

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

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