# SUMPRODUCT help

#### HondaTiger

##### Board Regular
Can someone explain why

=SUMPRODUCT((D4:D7=D10)*(E4:G7))

that formula works (where E4:G7 spans rows and columns)

and this formula does not?

=SUMPRODUCT((C4:C7=C13)*(D4:D7=D13),(E4:G7))

the second formula works if I change it to E4:E7, but I need to
include the entire table not just that one row.
aaa.xls
BCDEFG
3category1category2category3
4row1type1519
5row2type26210
6row3type17311
7row4type38412
8
9
10anycategoryanyrowtype136
11anycategoryanyrowtype218
12
13anycategoryrow1type1#VALUE!
14anycategoryrow2type10
Sheet1

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### NBVC

##### Well-known Member
Why not follow the same logic as your first sample and place an * before the last range.

=SUMPRODUCT((C4:C7=C13)*(D4:D7=D13)*(E4:G7))

#### Andrew Poulsom

##### MrExcel MVP
This works:

=SUMPRODUCT((C4:C7=C13)*(D4:D7=D13)*(E4:G7))

In the example you posted there a 2 arrays (because of the comma):

{1;0;0;0}
{5,1,9;6,2,10;7,3,11;8,4,12}

Because these aren't equal in size the formula returns #VALUE!.

In the formula above each array is multiplied to create one array:

{5,1,9;0,0,0;0,0,0;0,0,0}

and in the process the first 2 arrays are used 3 times (the number of columns in the last array).

Thanks guys!

Replies
2
Views
125
Replies
2
Views
296
Replies
0
Views
248
Replies
3
Views
122
Replies
4
Views
90

1,195,749
Messages
6,011,436
Members
441,614
Latest member
TiaGtz

### 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.

### Which adblocker are you using?

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

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