Sumproduct limitations

[scottylad2]

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

 

[Misca]

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

 

[Special-K99]

"Memtype" must have the same number of rows and columns as "Funds"
which is pretty much what Misca said

 

[scottylad2]

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]

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

 

[Brian from Maui]

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

 

[scottylad2]

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]

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]

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]

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.