sumproduct with non continuous ranges

[atrianacpa]

Hi Guys. I want to use sumproduct to add for me a non continuous range. For example, for the name Albert, I would like to add the sales from Jan thru March and then from June thru Aug.

A B C D E F G H I

Name Jan Feb Mar Apr May Jun Jul Aug

Albert 10 12 14 16 18 20 22 24

The answer should be : (10+12+14) + (20+22+24) = 102

I tried the following : sumproduct(--(A1:A2 ="Albert")*(B2:D2)*(G2:I2) But that gives me an NA and does not work. Any suggestions?

 

[Eric W]

Maybe:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Albert
Belle

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"]102[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=SUMPRODUCT(B2:I3*{1,1,1,0,0,1,1,1}*(A2:A3="Albert"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[atrianacpa]

Maybe:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Albert
Belle

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"]102[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=SUMPRODUCT(B2:I3*{1,1,1,0,0,1,1,1}*(A2:A3="Albert"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Wow, that was brilliant, assigning true and false values to each month included or excluded. Thank you !!

 

[Eric W]

Glad to help!

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF($A$2:$A$3="albert",$B$2:$D$3+$G$2:$I$3))