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
1	Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	Albert	10	12	14	16	18	20	22	24		102
3	Belle	1	2	3	4	5	6	7	8

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula K2 =SUMPRODUCT(B2:I3*{1,1,1,0,0,1,1,1}*(A2:A3="Albert")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[atrianacpa]

Maybe:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
1	Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	Albert	10	12	14	16	18	20	22	24		102
3	Belle	1	2	3	4	5	6	7	8

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula K2 =SUMPRODUCT(B2:I3*{1,1,1,0,0,1,1,1}*(A2:A3="Albert")) <tbody> </tbody>

<tbody>
</tbody>

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