Cell referencing another - Add or not add based on variables

[RodneyW]

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

 

[Eric W]

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

Book1
	D									M
1
2
3
4
5										3
6	1									1
7	1									2
8	1									2
9	1
10	1
11	1
12	1
13
14
15
16										17
Sheet5

Cell Formulas
Range	Formula
M16	M16	=SUMPRODUCT(D6:D15,M6:M15)+M5*COUNTIFS(D6:D15,">0",M6:M15,"")

 

[Fluff]

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

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

 

[RodneyW]

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

Book1
	D									M
1
2
3
4
5										3
6	1									1
7	1									2
8	1									2
9	1
10	1
11	1
12	1
13
14
15
16										17
Sheet5

Cell Formulas
Range	Formula
M16	M16	=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]

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]

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

Book1
	D									M
1
2
3
4
5										3
6	1									1
7	1									2
8	100									2
9	1
10	100
11	1
12	1
13
14
15
16										512
Sheet5

Cell Formulas
Range	Formula
M16	M16	=SUMPRODUCT(D6:D15*(M6:M15+M5*(M6:M15="")))

 

[RodneyW]

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]

Then this?

Book1
	D									M
1
2
3
4
5										3
6	1									1
7	1									2
8	100									2
9	1
10	100
11	1
12	1
13
14
15
16										17
Sheet5

Cell Formulas
Range	Formula
M16	M16	=SUMPRODUCT((D6:D15>0)*(M6:M15+M5*(M6:M15="")))

 

[RodneyW]

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

 

[Eric W]

Glad we could help!