SUMPRODUCT help

HondaTiger

Board Regular
Joined
Jul 9, 2002
Messages
53
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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))
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top