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>
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,588
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,344
"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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,298
Messages
5,510,471
Members
408,791
Latest member
bwirth

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top