usage of (--) or (-)

[vinod9111]

Hi all,

i have seen lot of formula especially sumproduct where "--" or "-" is used. i understand it is used for giving the conditions when the column has text or dates to be retrieved. But what decides two minus or single minus or no minus to be used.

If somebody can throw some light on this will be really helpful for my excel

 

[DonkeyOte]

If the dimensions in each "component" part of the Sumproduct are not of the same size then the double unary method will fail, another very simple example:

Excel Workbook
	A	B	C	D	E	F	G
1	Field 1	Field 2	Vals 1	Vals 2
2	x	a	12	77		Criteria 1	x
3	y	a	29	42		Criteria 2	a
4	z	c	41	77
5	x	a	55	62		-- Method	#VALUE!
6						* Method	206
Sheet1

Not the first 2 ranges used are 4x1 ... the last used is 4x2 ... e could of course use C2:C5+D2:D5 but if you had a large table of say 100 columns to sum that wouldn't really be viable... and of course in some instances you may have a more complex layout... this example is purely demo the basic concept behind the limitation.

 

[Aladin Akyurek]

SumProduct formulas that multiply vectors with matrices like this one:

=SUMPRODUCT(($A$2:$A$5=$G$2)*($B$2:$B$5=$G$3)*($C$2:$D$5))

are better avoided in favor of:

{=SUM(IF($A$2:$A$5=$G$2,IF($B$2:$B$5=$G$3,$C$2:$D$5)))}

because the latter naturally expands to:

{=SUM(IF($A$2:$A$5=$G$2,IF($B$2:$B$5=$G$3,IF(ISNUMBER($C$2:$D$5),$C$2:$D$5))))}

 

[exceluser2007]

Thanks Luke and Aladin for your clear explanations.

Aladin, I have one query though. For the particular example you gave, would the SUMPORODUCT((condition1)*(condition2)*(sumrange)) approach not be the better one, as we have established that using the (*) over the (--) version will only operate on a numeric "sumrange" i.e. do the IF(ISNUMBER(...)) in your equivalent array formula.

And also isn't there a calculation efficiency advantage of SUMPRODUCT over {SUM(IF(...}, from our previous discussions and not having to enter the SUMPRODUCT via Ctrl-Shift-Enter?

Just would like to really clarify this from the Gurus .

regards

 

[DonkeyOte]

I think we have a few crossed wires... the double unary approach effectively does the ISNUMBER test whereas the * method does not, the earlier example shows that using * on a range including text will generate VALUE errors, however, we can't always use the double unary approach as outlined.

Aladin's point is excellent and I confess that I had never thought of the benefits of a CSE approach, ie like you I have always adopted SUMPRODUCT over SUM(IF CSE's from the point of view that it is more robust... to illustrate Aladin's point & adapting the earlier example:

Excel Workbook
	A	B	C	D	E	F	G
1	Field 1	Field 2	Vals 1	Vals 2
2	x	a	12	77		Criteria 1	x
3	y	a	29	donkey		Criteria 2	a
4	z	c	41	77
5	x	a	55	62		-- Method	#VALUE!
6						* Method	#VALUE!
7						CSE	206
Sheet1

Given we have vectors (A2:A5, B2:B5) and a matrix (C2:D5) we can't use double unary (unless we add individually as outlined previously) ... so we use *, however, if the matrix contatins text values using the * method in a Sumproduct will generate VALUE error whereas using SUM CSE will not -- so here we can clearly see an example where a SUM(IF CSE is preferable to a Sumproduct.

Thanks Aladin.

 

[exceluser2007]

Thanks for the calrification Luke, sorry for any confusion caused Aladin.

So to summarise using these forumulas for conditional summing (for my general reference when approaching these types of problems henceforth):

1. If the "SumRange" is the same dimension as the "condition"
Ranges, then:
​

• • Use the SUMPRODUCT(--(condition1),--(condition2),(SumRange))
    • as this will work even if there is text in the SumRange

2. If the "SumRange" is of a larger dimension as the "condition"
Ranges and contains no text, then:
​

• • Use the SUMPRODUCT((condition1)*(condition2)*(SumRange))

3. If the "SumRange" is of a larger dimension as the "condition"
Ranges and contains text, then:
​

• • Use the {=SUM(IF(condition1,IF(condition2,SumRange)))}

Is this correct?

 

[DonkeyOte]

I'll let Aladin and co. give the definitive response (I learn from these guys after all) ... but ....

I would say "yes" though I would make the point that it's not a question of a specific range being larger another it's simply a case of if the ranges used are not consistent in terms of their dimensions the double unary approach is not viable....



I should like to make one final point re:dimensions and that is that there are odd occasions where the dimensions may be the same size once a given dimension is Transposed, eg:

Excel Workbook
	H	I	J	K	L
1			10	a	30
2	a	1
3	a	2
4	a	3
5			100
Sheet1

In this example the three ranges are 3x1, 3x1 & 1x3 ... by transposing the final range to 3x1 the double unary approach becomes viable (the Transpose requires CSE)

Over to Aladin & co for the closing argument(s)...

 

[theozz]

Hi ! Here Suggest to you.


<table style="border-collapse: separate;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="52"><td colspan="2"> DataSheet= Sheet1
Let's Look & Find [ --() useage ] </td></tr><tr><td align="right" width="30"><table style="table-layout: fixed; font-family: verdana;" rowspan="19" cellspacing="1" width="30"><tbody bgcolor="#ffffff"><tr height="18"><td>
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">1 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">2 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">3 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">4 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">5 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">6 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">7 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">8 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">9 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">10 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">11 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">12 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">13 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">14 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">15 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">16 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">17 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">18 </td></tr></tbody></table></td><td width="763"><table colspan="9" rowspan="9" style="table-layout: fixed; font-family: verdana;" bgcolor="#939393" border="0" cellspacing="1" width="763"><col width="84"><col width="92"><col width="92"><col width="92"><col width="23"><col width="84"><col width="84"><col width="84"><col width="84"><tbody bgcolor="#ffffff"><tr height="18"><td align="center" bgcolor="#dcdcdc">A</td><td align="center" bgcolor="#dcdcdc">B</td><td align="center" bgcolor="#dcdcdc">C</td><td align="center" bgcolor="#dcdcdc">D</td><td align="center" bgcolor="#dcdcdc">E</td><td align="center" bgcolor="#dcdcdc">F</td><td align="center" bgcolor="#dcdcdc">G</td><td align="center" bgcolor="#dcdcdc">H</td><td align="center" bgcolor="#dcdcdc">I</td></tr><tr height="18"><td colspan="4" align="center" bgcolor="#ffffff" width="360">Get Textual Numeric Summary</td><td bgcolor="#ffffff" width="23"> </td><td colspan="4" align="center" bgcolor="#ffffff" width="336">Want True's count</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">143</td><td bgcolor="#ccffcc" width="92">3</td><td align="right" bgcolor="#ccffcc" width="92">3</td><td align="right" bgcolor="#ccffcc" width="92">3</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">143</td><td align="center" bgcolor="#ccffcc" width="84">FALSE</td><td align="right" bgcolor="#ccffcc" width="84">0</td><td align="right" bgcolor="#ccffcc" width="84">0</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">161</td><td bgcolor="#ccffcc" width="92">1</td><td align="right" bgcolor="#ccffcc" width="92">1</td><td align="right" bgcolor="#ccffcc" width="92">1</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">161</td><td align="center" bgcolor="#ccffcc" width="84">TRUE</td><td align="right" bgcolor="#ccffcc" width="84">1</td><td align="right" bgcolor="#ccffcc" width="84">1</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">119</td><td bgcolor="#ccffcc" width="92">9</td><td align="right" bgcolor="#ccffcc" width="92">9</td><td align="right" bgcolor="#ccffcc" width="92">9</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">119</td><td align="center" bgcolor="#ccffcc" width="84">FALSE</td><td align="right" bgcolor="#ccffcc" width="84">0</td><td align="right" bgcolor="#ccffcc" width="84">0</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">157</td><td bgcolor="#ccffcc" width="92">7</td><td align="right" bgcolor="#ccffcc" width="92">7</td><td align="right" bgcolor="#ccffcc" width="92">7</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">157</td><td align="center" bgcolor="#ccffcc" width="84">TRUE</td><td align="right" bgcolor="#ccffcc" width="84">1</td><td align="right" bgcolor="#ccffcc" width="84">1</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">114</td><td bgcolor="#ccffcc" width="92">4</td><td align="right" bgcolor="#ccffcc" width="92">4</td><td align="right" bgcolor="#ccffcc" width="92">4</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">114</td><td align="center" bgcolor="#ccffcc" width="84">FALSE</td><td align="right" bgcolor="#ccffcc" width="84">0</td><td align="right" bgcolor="#ccffcc" width="84">0</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">135</td><td bgcolor="#ccffcc" width="92">5</td><td align="right" bgcolor="#ccffcc" width="92">5</td><td align="right" bgcolor="#ccffcc" width="92">5</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">135</td><td align="center" bgcolor="#ccffcc" width="84">FALSE</td><td align="right" bgcolor="#ccffcc" width="84">0</td><td align="right" bgcolor="#ccffcc" width="84">0</td></tr><tr height="18"><td bgcolor="#ffffff" width="84"> </td><td align="right" bgcolor="#ccffcc" width="92">0</td><td align="right" bgcolor="#ccffcc" width="92">29</td><td align="right" bgcolor="#ccffcc" width="92">29</td><td bgcolor="#ffffff" width="23"> </td><td bgcolor="#ffffff" width="84"> </td><td align="right" bgcolor="#ccffcc" width="84">0</td><td align="right" bgcolor="#ccffcc" width="84">2</td><td align="right" bgcolor="#ccffcc" width="84">2</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">143</td><td bgcolor="#ffcc00" width="92">3</td><td align="right" bgcolor="#ffcc00" width="92">3</td><td align="right" bgcolor="#ffcc00" width="92">3</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">143</td><td align="center" bgcolor="#ffcc00" width="84">FALSE</td><td align="right" bgcolor="#ffcc00" width="84">0</td><td align="right" bgcolor="#ffcc00" width="84">0</td></tr></tbody></table><table colspan="9" rowspan="9" style="table-layout: fixed; font-family: verdana;" bgcolor="#939393" border="0" cellspacing="1" width="763"><col width="84"><col width="92"><col width="92"><col width="92"><col width="23"><col width="84"><col width="84"><col width="84"><col width="84"><tbody bgcolor="#ffffff"><tr height="18"><td align="right" bgcolor="#ffffff" width="84">161</td><td bgcolor="#ffcc00" width="92">1</td><td align="right" bgcolor="#ffcc00" width="92">1</td><td align="right" bgcolor="#ffcc00" width="92">1</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">161</td><td align="center" bgcolor="#ffcc00" width="84">TRUE</td><td align="right" bgcolor="#ffcc00" width="84">1</td><td align="right" bgcolor="#ffcc00" width="84">1</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">119</td><td bgcolor="#ffcc00" width="92">9</td><td align="right" bgcolor="#ffcc00" width="92">9</td><td align="right" bgcolor="#ffcc00" width="92">9</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">119</td><td align="center" bgcolor="#ffcc00" width="84">FALSE</td><td align="right" bgcolor="#ffcc00" width="84">0</td><td align="right" bgcolor="#ffcc00" width="84">0</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">157</td><td bgcolor="#ffcc00" width="92">7</td><td align="right" bgcolor="#ffcc00" width="92">7</td><td align="right" bgcolor="#ffcc00" width="92">7</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">157</td><td align="center" bgcolor="#ffcc00" width="84">TRUE</td><td align="right" bgcolor="#ffcc00" width="84">1</td><td align="right" bgcolor="#ffcc00" width="84">1</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">114</td><td bgcolor="#ffcc00" width="92">4</td><td align="right" bgcolor="#ffcc00" width="92">4</td><td align="right" bgcolor="#ffcc00" width="92">4</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">114</td><td align="center" bgcolor="#ffcc00" width="84">FALSE</td><td align="right" bgcolor="#ffcc00" width="84">0</td><td align="right" bgcolor="#ffcc00" width="84">0</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="84">135</td><td bgcolor="#ffcc00" width="92">5</td><td align="right" bgcolor="#ffcc00" width="92">5</td><td align="right" bgcolor="#ffcc00" width="92">5</td><td bgcolor="#ffffff" width="23"> </td><td align="right" bgcolor="#ffffff" width="84">135</td><td align="center" bgcolor="#ffcc00" width="84">FALSE</td><td align="right" bgcolor="#ffcc00" width="84">0</td><td align="right" bgcolor="#ffcc00" width="84">0</td></tr><tr height="18"><td bgcolor="#ffffff" width="84"> </td><td align="right" bgcolor="#ccffcc" width="92">0</td><td align="right" bgcolor="#ccffcc" width="92">29</td><td align="right" bgcolor="#ccffcc" width="92">29</td><td bgcolor="#ffffff" width="23"> </td><td bgcolor="#ffffff" width="84"> </td><td align="right" bgcolor="#ccffcc" width="84">0</td><td align="right" bgcolor="#ccffcc" width="84">2</td><td align="right" bgcolor="#ccffcc" width="84">2</td></tr><tr height="18"><td bgcolor="#ffffff" width="84"> </td><td align="center" bgcolor="#ccffcc" width="92">#VALUE!</td><td align="center" bgcolor="#ccffcc" width="92">#VALUE!</td><td align="center" bgcolor="#ccffcc" width="92">#VALUE!</td><td bgcolor="#ffffff" width="23"> </td><td bgcolor="#ffffff" width="84"> </td><td bgcolor="#ffffff" width="84"> </td><td align="right" bgcolor="#ccffcc" width="84">2</td><td align="right" bgcolor="#ccffcc" width="84">2</td></tr><tr height="18"><td bgcolor="#ffffff" width="84"> </td><td align="right" bgcolor="#ffcc00" width="92">0</td><td align="right" bgcolor="#ffcc00" width="92">29</td><td align="right" bgcolor="#ffcc00" width="92">29</td><td bgcolor="#ffffff" width="23"> </td><td bgcolor="#ffffff" width="84"> </td><td bgcolor="#ffffff" width="84"> </td><td bgcolor="#ffffff" width="84"> </td><td bgcolor="#ffffff" width="84"> </td></tr><tr height="18"><td bgcolor="#ffffff" width="84"> </td><td align="right" bgcolor="#ccffcc" width="92">0</td><td align="right" bgcolor="#ccffcc" width="92">29</td><td align="right" bgcolor="#ccffcc" width="92">29</td><td bgcolor="#ffffff" width="23"> </td><td bgcolor="#ffffff" width="84"> </td><td bgcolor="#ffffff" width="84"> </td><td bgcolor="#ffffff" width="84"> </td><td bgcolor="#ffffff" width="84"> </td></tr></tbody></table></td></tr></tbody></table>
<colspan=5 width="800" rowspan="32" height="576"></colspan=5><table style="border-collapse: separate;" border="6" cellspacing="0"><tbody><tr height="24"><td colspan="5" align="center">Used Formula ...(With Running MicrosoftExcel Ver 2003)
If Pink Formula that is FormulaArray !!!
WithOut {} Input Formula and Press Control+Shift+Enter</td></tr><tr height="24"><td align="center" bgcolor="#d3d3d3" width="4%">No</td><td align="center" bgcolor="#d3d3d3" width="9%">Addr'</td><td align="center" bgcolor="#d3d3d3" width="65%"> If use below Formula, You'll Get Result as Right</td><td align="center" bgcolor="#d3d3d3" width="15%">Result</td><td align="center" bgcolor="#d3d3d3">Formula's</td></tr><tr height="20"><td align="center">1</td><td align="center" bgcolor="#ccffcc">B2</td><td bgcolor="#ccffcc">=RIGHT(A2,1)</td><td>3</td><td align="right"><form name="PrNames1"><input *******="window.clipboardData.setData("Text","=RIGHT(A2,1)");" value="Do Copy" name="MyNames1" type="button"></form></td></tr><tr height="20"><td align="center">2</td><td align="center" bgcolor=""> </td><td>B2 His Formula Used This Cell -> B2:B7</td><td> </td><td> </td></tr><tr height="20"><td align="center">3</td><td align="center" bgcolor="#ccffcc">C2</td><td bgcolor="#ccffcc">=RIGHT(A2,1)*1</td><td align="right">3</td><td align="right"><form name="PrNames3"><input *******="window.clipboardData.setData("Text","=RIGHT(A2,1)*1");" value="Do Copy" name="MyNames3" type="button"></form></td></tr><tr height="20"><td align="center">4</td><td align="center" bgcolor=""> </td><td>C2 His Formula Used This Cell -> C2:C7</td><td> </td><td> </td></tr><tr height="20"><td align="center">5</td><td align="center" bgcolor="#ccffcc">D2</td><td bgcolor="#ccffcc">=--RIGHT(A2,1)</td><td align="right">3</td><td align="right"><form name="PrNames5"><input *******="window.clipboardData.setData("Text","=--RIGHT(A2,1)");" value="Do Copy" name="MyNames5" type="button"></form></td></tr><tr height="20"><td align="center">6</td><td align="center" bgcolor=""> </td><td>D2 His Formula Used This Cell -> D2:D7</td><td> </td><td> </td></tr><tr height="20"><td align="center">7</td><td align="center" bgcolor="#ccffcc">G2</td><td bgcolor="#ccffcc">=F2＞150</td><td align="center">FALSE</td><td align="right"><form name="PrNames7"><input *******="window.clipboardData.setData("Text","=F2>150");" value="Do Copy" name="MyNames7" type="button"></form></td></tr><tr height="20"><td align="center">8</td><td align="center" bgcolor=""> </td><td>G2 His Formula Used This Cell -> G2:G7</td><td> </td><td> </td></tr><tr height="20"><td align="center">9</td><td align="center" bgcolor="#ccffcc">H2</td><td bgcolor="#ccffcc">=(F2＞150)*1</td><td align="right">0</td><td align="right"><form name="PrNames9"><input *******="window.clipboardData.setData("Text","=(F2>150)*1");" value="Do Copy" name="MyNames9" type="button"></form></td></tr><tr height="20"><td align="center">10</td><td align="center" bgcolor=""> </td><td>H2 His Formula Used This Cell -> H2:H7</td><td> </td><td> </td></tr><tr height="20"><td align="center">11</td><td align="center" bgcolor="#ccffcc">I2</td><td bgcolor="#ccffcc">=--(F2＞150)</td><td align="right">0</td><td align="right"><form name="PrNames11"><input *******="window.clipboardData.setData("Text","=--(F2>150)");" value="Do Copy" name="MyNames11" type="button"></form></td></tr><tr height="20"><td align="center">12</td><td align="center" bgcolor=""> </td><td>I2 His Formula Used This Cell -> I2:I7</td><td> </td><td> </td></tr><tr height="20"><td align="center">13</td><td align="center" bgcolor="#ccffcc">B8</td><td bgcolor="#ccffcc">=SUM(B2:B7)</td><td align="right">0</td><td align="right"><form name="PrNames13"><input *******="window.clipboardData.setData("Text","=SUM(B2:B7)");" value="Do Copy" name="MyNames13" type="button"></form></td></tr><tr height="20"><td align="center">14</td><td align="center" bgcolor=""> </td><td>B8 His Formula Used This Cell -> B8:D8,G8:I8,B15:D15,G15:I15</td><td> </td><td> </td></tr><tr height="20"><td align="center">15</td><td align="center" bgcolor="#ffcc00">B9:B14</td><td bgcolor="#ffcc00">=RIGHT(A9:A14,1)
Select All Range and Input Formula </td><td>3</td><td align="right"><form name="PrNames15"><input *******="window.clipboardData.setData("Text","=RIGHT(A9:A14,1)");" value="Do Copy" name="MyNames15" type="button"></form></td></tr><tr height="20"><td align="center">16</td><td align="center" bgcolor="#ffcc00">C9:C14</td><td bgcolor="#ffcc00">=RIGHT(A9:A14,1)*1
Select All Range and Input Formula </td><td align="right">3</td><td align="right"><form name="PrNames16"><input *******="window.clipboardData.setData("Text","=RIGHT(A9:A14,1)*1");" value="Do Copy" name="MyNames16" type="button"></form></td></tr><tr height="20"><td align="center">17</td><td align="center" bgcolor="#ffcc00">D9:D14</td><td bgcolor="#ffcc00">=--RIGHT(A9:A14,1)
Select All Range and Input Formula </td><td align="right">3</td><td align="right"><form name="PrNames17"><input *******="window.clipboardData.setData("Text","=--RIGHT(A9:A14,1)");" value="Do Copy" name="MyNames17" type="button"></form></td></tr><tr height="20"><td align="center">18</td><td align="center" bgcolor="#ffcc00">G9:G14</td><td bgcolor="#ffcc00">=F9:F14＞150
Select All Range and Input Formula </td><td align="center">FALSE</td><td align="right"><form name="PrNames18"><input *******="window.clipboardData.setData("Text","=F9:F14>150");" value="Do Copy" name="MyNames18" type="button"></form></td></tr><tr height="20"><td align="center">19</td><td align="center" bgcolor="#ffcc00">H9:H14</td><td bgcolor="#ffcc00">=(F9:F14＞150)*1
Select All Range and Input Formula </td><td align="right">0</td><td align="right"><form name="PrNames19"><input *******="window.clipboardData.setData("Text","=(F9:F14>150)*1");" value="Do Copy" name="MyNames19" type="button"></form></td></tr><tr height="20"><td align="center">20</td><td align="center" bgcolor="#ffcc00">I9:I14</td><td bgcolor="#ffcc00">=--(F9:F14＞150)
Select All Range and Input Formula </td><td align="right">0</td><td align="right"><form name="PrNames20"><input *******="window.clipboardData.setData("Text","=--(F9:F14>150)");" value="Do Copy" name="MyNames20" type="button"></form></td></tr><tr height="20"><td align="center">21</td><td align="center" bgcolor="#ccffcc">B16</td><td bgcolor="#ccffcc">=SUM(RIGHT(A9:A14,1))</td><td align="center">#VALUE!</td><td align="right"><form name="PrNames21"><input *******="window.clipboardData.setData("Text","=SUM(RIGHT(A9:A14,1))");" value="Do Copy" name="MyNames21" type="button"></form></td></tr><tr height="20"><td align="center">22</td><td align="center" bgcolor="#ccffcc">C16</td><td bgcolor="#ccffcc">=SUM(RIGHT(A9:A14,1)*1)</td><td align="center">#VALUE!</td><td align="right"><form name="PrNames22"><input *******="window.clipboardData.setData("Text","=SUM(RIGHT(A9:A14,1)*1)");" value="Do Copy" name="MyNames22" type="button"></form></td></tr><tr height="20"><td align="center">23</td><td align="center" bgcolor="#ccffcc">D16</td><td bgcolor="#ccffcc">=SUM(--RIGHT(A9:A14,1))</td><td align="center">#VALUE!</td><td align="right"><form name="PrNames23"><input *******="window.clipboardData.setData("Text","=SUM(--RIGHT(A9:A14,1))");" value="Do Copy" name="MyNames23" type="button"></form></td></tr><tr height="20"><td align="center">24</td><td align="center" bgcolor="#ccffcc">H16</td><td bgcolor="#ccffcc">=SUMPRODUCT((F9:F14＞150)*1)</td><td align="right">2</td><td align="right"><form name="PrNames24"><input *******="window.clipboardData.setData("Text","=SUMPRODUCT((F9:F14>150)*1)");" value="Do Copy" name="MyNames24" type="button"></form></td></tr><tr height="20"><td align="center">25</td><td align="center" bgcolor="#ccffcc">I16</td><td bgcolor="#ccffcc">=SUMPRODUCT(--(F9:F14＞150))</td><td align="right">2</td><td align="right"><form name="PrNames25"><input *******="window.clipboardData.setData("Text","=SUMPRODUCT(--(F9:F14>150))");" value="Do Copy" name="MyNames25" type="button"></form></td></tr><tr height="20"><td align="center">26</td><td align="center" bgcolor="#ffcc00">B17</td><td bgcolor="#ffcc00">=SUM(RIGHT(A9:A14,1))</td><td align="right">0</td><td align="right"><form name="PrNames26"><input *******="window.clipboardData.setData("Text","=SUM(RIGHT(A9:A14,1))");" value="Do Copy" name="MyNames26" type="button"></form></td></tr><tr height="20"><td align="center">27</td><td align="center" bgcolor="#ffcc00">C17</td><td bgcolor="#ffcc00">=SUM(RIGHT(A9:A14,1)*1)</td><td align="right">29</td><td align="right"><form name="PrNames27"><input *******="window.clipboardData.setData("Text","=SUM(RIGHT(A9:A14,1)*1)");" value="Do Copy" name="MyNames27" type="button"></form></td></tr><tr height="20"><td align="center">28</td><td align="center" bgcolor="#ffcc00">D17</td><td bgcolor="#ffcc00">=SUM(--RIGHT(A9:A14,1))</td><td align="right">29</td><td align="right"><form name="PrNames28"><input *******="window.clipboardData.setData("Text","=SUM(--RIGHT(A9:A14,1))");" value="Do Copy" name="MyNames28" type="button"></form></td></tr><tr height="20"><td align="center">29</td><td align="center" bgcolor="#ccffcc">B18</td><td bgcolor="#ccffcc">=SUMPRODUCT(RIGHT(A9:A14,1))</td><td align="right">0</td><td align="right"><form name="PrNames29"><input *******="window.clipboardData.setData("Text","=SUMPRODUCT(RIGHT(A9:A14,1))");" value="Do Copy" name="MyNames29" type="button"></form></td></tr><tr height="20"><td align="center">30</td><td align="center" bgcolor="#ccffcc">C18</td><td bgcolor="#ccffcc">=SUMPRODUCT(RIGHT(A9:A14,1)*1)</td><td align="right">29</td><td align="right"><form name="PrNames30"><input *******="window.clipboardData.setData("Text","=SUMPRODUCT(RIGHT(A9:A14,1)*1)");" value="Do Copy" name="MyNames30" type="button"></form></td></tr><tr height="20"><td align="center">31</td><td align="center" bgcolor="#ccffcc">D18</td><td bgcolor="#ccffcc">=SUMPRODUCT(--RIGHT(A9:A14,1))</td><td align="right">29</td><td align="right"><form name="PrNames31"><input *******="window.clipboardData.setData("Text","=SUMPRODUCT(--RIGHT(A9:A14,1))");" value="Do Copy" name="MyNames31" type="button"></form></td></tr><tr height="24"><td colspan="5">.</td></tr></tbody></table>

 

[Aladin Akyurek]

Thanks for the calrification Luke, sorry for any confusion caused Aladin.

So to summarise using these forumulas for conditional summing (for my general reference when approaching these types of problems henceforth):

1. If the "SumRange" is the same dimension as the "condition"
Ranges, then:
​

• • Use the SUMPRODUCT(--(condition1),--(condition2),(SumRange))
    • as this will work even if there is text in the SumRange

2. If the "SumRange" is of a larger dimension as the "condition"
Ranges and contains no text, then:
​

• • Use the SUMPRODUCT((condition1)*(condition2)*(SumRange))

3. If the "SumRange" is of a larger dimension as the "condition"
Ranges and contains text, then:
​

• • Use the {=SUM(IF(condition1,IF(condition2,SumRange)))}

Is this correct?

Lets define ranges like

A2:A10, D2:I2, F:F, and 3:3

a vector range, and

A2:I10, F:G, and 3:4

a matrix range.

Since (2) forces one additionally to assess if there is any text in the matrix range to sum, it's more appropriate/secure to switch to, using your notation:

{=SUM(IF(condition1,IF(condition2,SumRange)))}

There are two forms:

[A]

=SUMPRODUCT(--(condition1),--(condition2),SumRange)



{=SUM(IF(condition1,IF(condition2,SumRange)))}

Note that the SUM(IF construct is the most general: [A] becomes...

=SUM(IF(condition1,IF(condition2,SumRange)

There is no reason to invoke a SUMPRODUCT formula when an IF expression or a TRANSPOSE expression is needed. But, take Luke's TRANSPOSE example:

=SUMPRODUCT(--($H$2:$H$4="a"),$I$2:$I$4,TRANSPOSE($J$1:$L$1))

which must be confirmed with control+shift+enter (CSE), due to the TRANSPOSE call, while a SumProduct formula is often invoked because of no need for CSE. Given the foregoing consideration, the SUM(IF construct would have been more appropriate:

=SUM(IF($H$2:$H$4="a",$I$2:$I$4*TRANSPOSE($J$1:$L$1))

but the presence of the text values in one or more ranges to multiply forces to us to set up something like:

=SUM(
   IF($H$2:$H$4="a",
    $I$2:$I$4*IF(ISNUMBER(TRANSPOSE($J$1:$L$1)),
     TRANSPOSE($J$1:$L$1))))

Instead of calling TRANSPOSE twice, it's better to invoke a usual SumProduct formula, that is, the usual vector version:

{=SUMPRODUCT(--($H$2:$H$4="a"),$I$2:$I$4,TRANSPOSE($J$1:$L$1))}

 

[Colin Legg]

The SUM CSE formula can be improved by transposing the vertical result vector rather than calling TRANSPOSE() twice within the IF() call....

=SUM(TRANSPOSE(($H$2:$H$4="a")*($I$2:$I$4))
    *IF(ISNUMBER($J$1:$L$1),$J$1:$L$1))

 

[khbutts]

I am sorry if this is the wrong thread to ask this but does anyone experience MUCH slower calculations when they use SUMPRODUCT in this way? I was wondering if there is anything I can do to keep the functionality of this function but just speed up the processing? It seems to be a major resource hog and slows me down considerably.

Anyone else having this issue and figured out a way to speed things up?