Cell referencing another - Add or not add based on variables

RodneyW

Active Member
Joined
Sep 24, 2010
Messages
341
Office Version
  1. 2013
Platform
  1. Windows
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.

Thank you in advance
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,811
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
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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’)

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

RodneyW

Active Member
Joined
Sep 24, 2010
Messages
341
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Sep 24, 2010
Messages
341
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,811
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
Joined
Sep 24, 2010
Messages
341
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,811
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="")))
 
Solution

RodneyW

Active Member
Joined
Sep 24, 2010
Messages
341
Office Version
  1. 2013
Platform
  1. Windows
YAHTZEE! Thanks for the help. I appreciate you sticking with it
 

Watch MrExcel Video

Forum statistics

Threads
1,129,696
Messages
5,637,862
Members
416,986
Latest member
zmartee

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