# Cell referencing another - Add or not add based on variables

#### RodneyW

##### Active Member
I apologize in advance for the lengthy post but wanted to explain as much as I think is needed.

End goal is to have a formula in cell M16 that will total cells M6 through M15. Cells M6 - M15 will either have a manual entry OR, and more likely, not have a manual entry, but for calculation purposes (not for display purposes) carry the value entered in cell M5.

Values in M6 - M15 are used in column N with this, or similar formula referencing the appropriate M and D cells

=IF(M6="",\$M\$5*D6,\$M6*D6) (Note the reference to D6. If D6 is empty, the value in N6 will also be empty)

If there is no manually entered value in any cell M6 - M15, it will display as empty, but use the value in M5 for calculation in M16 with the two exceptions below

Let's say M5 has the value of 3. The value in M5 will apply to calculations representing each cell M6 - M15 unless/ or
1. A specific cell M6 - M15 has a manually entered number
2. A specific call D6 - D15 is empty. Column D is used to calculate several other equations but if it's empty, all calculations referencing column D should be null.

For example calculations, let's say:
M5 has a value of 3
M6 has a manually entered value of 1 (for calculation purposes, this will count as 1)
M7 and M8 have a manually entered value of 2 (for calculation purposes, these each will count as 2)
M9 - M12 have no manually entered value and are left empty (for calculation purposes, these cells will take on the value in M5, in this case will count as 3)
M13 - M15 will not be included in the M16 calculation because D13 - D15 are empty

So, the total displayed in M16 would be 17 because
M6 = 1 + M7 = 2 +M8 = 2 + M9 = 3 + M10 = 3 + M11 = 3 + M12 = 3
M13, M14 and M15 would not add any value because D13 - D15 are empty.

I need a formula for cell M16 that would calculate as outlined above.

Maybe here's another way to outline it:
The first thing the formula should look at is to see whether or not D has any value =IF(D6="") If this is true then M6 should carry the value of 0 when calculating the total in M16.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Eric W

##### MrExcel MVP
Assuming your values in D are 1 or nothing, then:

Book1
DM
1
2
3
4
53
611
712
812
91
101
111
121
13
14
15
1617
Sheet5
Cell Formulas
RangeFormula
M16M16=SUMPRODUCT(D6:D15,M6:M15)+M5*COUNTIFS(D6:D15,">0",M6:M15,"")

#### Fluff

##### MrExcel MVP, Moderator
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Excel Formula:
``=SUMPRODUCT((D6:D15<>"")*(IF(M6:M15="",M5,M6:M15)))``

#### RodneyW

##### Active Member
Assuming your values in D are 1 or nothing, then:

Book1
DM
1
2
3
4
53
611
712
812
91
101
111
121
13
14
15
1617
Sheet5
Cell Formulas
RangeFormula
M16M16=SUMPRODUCT(D6:D15,M6:M15)+M5*COUNTIFS(D6:D15,">0",M6:M15,"")
Eric, thanks for the reply. The values in D could be anything, usually they will be tens or hundreds of thousands. Think of column D as a toggle. It's either on (count as 1) or off (count as 0)

#### RodneyW

##### Active Member

Eric, thanks for the reply. The values in D could be anything, usually they will be tens or hundreds of thousands. Think of column D as a toggle. It's either on (count as the value in M5 or the manually entered value in M6 - M15) or off (count as 0)

#### Eric W

##### MrExcel MVP
But you are multiplying by the value in D, right? How about:

Book1
DM
1
2
3
4
53
611
712
81002
91
10100
111
121
13
14
15
16512
Sheet5
Cell Formulas
RangeFormula
M16M16=SUMPRODUCT(D6:D15*(M6:M15+M5*(M6:M15="")))

#### RodneyW

##### Active Member

Actually, no., not for column M. For column N, yes. For column M, think of D as an on/off switch. If column D has any entry at all (think of this as turned "on, then cell M16 will add the value manually entered into M6, M7 etc. OR the value entered into M5 if no value is manually entered into M6 - M15. If any cell D6 - D15 have no value (think of this as turned "off"), then the corresponding cell M6 - M15 would likewise count as 0 in the M16 calculation.

#### Eric W

##### MrExcel MVP
Then this?

Book1
DM
1
2
3
4
53
611
712
81002
91
10100
111
121
13
14
15
1617
Sheet5
Cell Formulas
RangeFormula
M16M16=SUMPRODUCT((D6:D15>0)*(M6:M15+M5*(M6:M15="")))

#### RodneyW

##### Active Member
YAHTZEE! Thanks for the help. I appreciate you sticking with it

Replies
0
Views
89
Replies
5
Views
36
Replies
1
Views
895
Replies
1
Views
129
Replies
15
Views
83

1,127,519
Messages
5,625,280
Members
416,086
Latest member
CaptainGD

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