Sumproduct limitations

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
<table style="border-collapse: collapse; width: 357pt;" border="0" cellpadding="0" cellspacing="0" width="477"><col style="width: 205pt;" width="274"> <col style="width: 80pt;" width="107"> <col style="width: 72pt;" width="96"> <tbody><tr style="height: 14.4pt;" height="19"> <td class="xl63" style="height: 14.4pt; width: 205pt;" height="19" width="274">Member Categories</td> <td class="xl64" style="width: 80pt;" width="107">
</td> <td class="xl65" style="width: 72pt;" width="96">
</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt;" height="19">Playing Member</td> <td align="center">#N/A</td> <td class="xl67">£64,470.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl68" style="height: 14.4pt;" height="19">Playing Member Student/21</td> <td align="center">#N/A</td> <td class="xl67">£350.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt;" height="19">playing Member Under 18</td> <td align="center">#N/A</td> <td class="xl67">£100.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt;" height="19">Non Playing Member</td> <td align="center">#N/A</td> <td class="xl67">£770.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt;" height="19">Non Playing Member Under 18</td> <td align="center">#N/A</td> <td class="xl67">£100.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt;" height="19">Family Member Paying</td> <td align="center">#N/A</td> <td class="xl67">£900.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt;" height="19">Family member Paid by other</td> <td align="center">#N/A</td> <td class="xl67">£0.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt;" height="19">Family Member 1 plus 2 under 18 Paying</td> <td align="center">#N/A</td> <td class="xl67">£200.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl66" style="height: 14.4pt;" height="19">Associate Member (Stoneyhill Comm Centre)</td> <td align="center">#N/A</td> <td class="xl67">£60.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">
Does sumproduct have small limits to what data it can sum? The two formulas above have been done on the same data, Sumif seems to work no problem but sumproduct has failed......any ideas why? Column C has SUMIF(MemType,A2,Funds) and referring to identical range SUMPRODUCT(--(MemType=A2),Funds) but it's returning #N/A is it because the named ranges are on a different sheet?
</td> <td class="xl70">
</td> <td class="xl71">
</td> </tr> </tbody></table>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,697
The sumproduct checks that the all the given rows (or columns) match the criteria. The ranges don't have to be on the same sheet (or even start from the same row) but all the ranges have to have as many cells (=rows/columns) in them:

This one seems to work just fine:

=SUMPRODUCT(--($A$26:$A$48=$A2),(Sheet3!$C$1:$C$23))
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
"Memtype" must have the same number of rows and columns as "Funds"
which is pretty much what Misca said
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Yes that much i already know. I've got 1000 rows, in 2 columns. Ranges are the same dimensions, but sumproducts giving me an error message. I would have though 1000 rows was within the ranges for it, but maybe not. I#ve deleted the Names and went with cell references but it's not making any difference. Will have to stay with sumif i guess
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

<table style="border-collapse: collapse; width: 802pt;" border="0" cellpadding="0" cellspacing="0" width="1069"><col style="width: 205pt;" width="274"> <col style="width: 64pt;" width="85"> <col style="width: 86pt;" width="114"> <col style="width: 76pt;" width="101"> <col style="width: 205pt;" width="274"> <col style="width: 112pt;" width="149"> <col style="width: 54pt;" width="72"> <tbody><tr style="height: 14.4pt;" height="19"> <td class="xl76" style="height: 14.4pt; width: 205pt;" height="19" width="274">Category</td> <td class="xl77" style="border-left: medium none; width: 64pt;" width="85">Cost</td> <td class="xl77" style="border-left: medium none; width: 86pt;" width="114">Fully Paid</td> <td class="xl78" style="border-left: medium none; width: 76pt;" width="101">Status</td> <td class="xl71" style="width: 205pt;" width="274"> </td> <td class="xl71" style="width: 112pt;" width="149"> </td> <td class="xl79" style="width: 54pt;" width="72"> </td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">Family member Paid by other</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£0.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Yes</td> <td class="xl68" style="border-left: medium none;">To Pay</td> <td>
</td> <td align="right">0</td> <td class="xl81"> </td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">Playing Member Student/21</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£35.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Yes</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">To Pay</td> <td>
</td> <td>
</td> <td class="xl81"> </td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">Family Member Paying</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£75.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Fully Paid Up</td> <td class="xl72">Playing Member</td> <td class="xl82">#N/A</td> <td class="xl73">£64,400.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">playing Member Under 18</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£25.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Fully Paid Up</td> <td class="xl74">Playing Member Student/21</td> <td class="xl82">#N/A</td> <td class="xl73">£385.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">Non Playing Member Under 18</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£25.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Fully Paid Up</td> <td class="xl72">playing Member Under 18</td> <td class="xl82">#N/A</td> <td class="xl73">£100.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">Non Playing Member</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£35.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Fully Paid Up</td> <td class="xl72">Non Playing Member</td> <td class="xl82">#N/A</td> <td class="xl73">£770.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-left: 1pt solid windowtext; border-right: 0.5pt solid windowtext; border-bottom: 0.5pt solid windowtext; background: red none repeat scroll 0% 0%; height: 14.4pt; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="19">Family Member Paying</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: red none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">£75.00</td> <td class="xl67" style="border: 0.5pt solid windowtext; background: red none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">No</td> <td class="xl69" style="border: 0.5pt solid windowtext; background: red none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">To Pay</td> <td class="xl72" style="border-style: none none none solid; border-color: -moz-use-text-color -moz-use-text-color -moz-use-text-color windowtext; border-width: medium medium medium 1pt; background: red none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">Non Playing Member Under 18</td> <td class="xl82">#N/A</td> <td class="xl73">£100.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">Family member Paid by other</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£0.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Fully Paid Up</td> <td class="xl72">Family Member Paying</td> <td class="xl82">#N/A</td> <td class="xl73">£900.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">Family member Paid by other</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£0.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Fully Paid Up</td> <td class="xl72">Family member Paid by other</td> <td class="xl82">#N/A</td> <td class="xl73">£0.00</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl80" style="border-top: medium none; height: 14.4pt;" height="19">Playing Member Student/21</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">£35.00</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Fully Paid Up</td> <td class="xl72">Family Member 1 plus 2 under 18 Paying</td> <td class="xl82">#N/A</td> <td class="xl73">£200.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl83" style="border-top: medium none; height: 15pt;" height="20">Playing Member Student/21</td> <td class="xl84" style="border-top: medium none; border-left: medium none;">£35.00</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl85" style="border-top: medium none; border-left: medium none;">Fully Paid Up</td> <td class="xl75">Associate Member (Stoneyhill Comm Centre)</td> <td class="xl86">#N/A</td> <td class="xl87">£60.00</td> </tr> </tbody></table>same formula as in my earlier post, #N/A resulst the same though
SUMPRODUCT(--($I$2:$I$1000=M4),$J$2:$J$1000)

SUMIF($I$2:$I$1000,M4,$J$2:$J$1000)
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

What formula are you using and are there any error values in any columns of interest?

The categories list is populated by a Dada validation list, while the amounts to be summed are pulled through from a Vlookup. If i try on smaller sectins it works, just seems to be a problem when i sum the ranges
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
1. If Sumif works....Use it, it is preferred. You only need sumproduct when you have multiple criteria.

2. If there are any formulas resuling in an error within any range referenced in the sumproduct formula, it will error. I think that's what Brian was going after.
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
I'll probably have to stick with Sumifs, just curious as to why it stopped. Don't see any errors, but thats maybe coz i've not discovered them so far lol
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Well, the only reason I can imagine that

This works
SUMIF($I$2:$I$1000,M4,$J$2:$J$1000)

But this does not
SUMPRODUCT(--($I$2:$I$1000=M4),$J$2:$J$1000)

Is a Formula Error within either 2 ranges I2:I1000 or J2:J2000


If you cannot see any, I have seen before where someone uses a custom format to HIDE errors...Perhaps that is happening.

Use =ISERROR(I2) and Fill down - repeat for column J.

I'm betting you'll find at least 1.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,169
Members
418,430
Latest member
Chlwls808

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
Top