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>

 

[scottylad2]

Re: Sumproduct limitations-Resolved

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.

Well what do you know, Brian was right. Scrolled through my 1000 rows and there was an error, I'd altered something on my Validation List and hadn't updated one of the cells in my member category so it was indeed showing as something Not Available....Cheers Brian and the extra promt jonmo1

 

[scottylad2]

Excel Workbook
	A	B	C
1	Member Categories
2	Playing Member	64,400.00	64,400.00
3	Playing Member Student/21	420.00	420.00
4	playing Member Under 18	125.00	125.00
5	Non Playing Member	770.00	770.00
6	Non Playing Member Under 18	100.00	100.00
7	Family Member Paying	900.00	900.00
8	Family member Paid by other	0.00	0.00
9	Family Member 1 plus 2 under 18 Paying	200.00	200.00
10	Associate Member (Stoneyhill Comm Centre)	60.00	60.00
11
Sheet2

Excel Workbook
	C
2	64,400.00
Sheet2

 

[Jonmo1]

Re: Sumproduct limitations-Resolved

Cool, glad you got it working...

Now, why does sumif work in that situation where sumproduct does not?..


Because sumif actually ignores each row that does not meet the criteria..
But sumproduct does not, it still does the math on each and every row.
The only difference is that 0 is added to the sum when criteria is not met, but the math is literally still done, just multiplied by 0.

 

[scottylad2]

I'm surprised it ignores errors. Sumproduct suits better because i'm making the thing for someone that has Excel 2003 and there will be more than 1 criteria that needs to be summed at some poiint.

learn something new from this board

thanks again

 

[Jonmo1]

Sumif doesn't ignore errors...per se..

It ignores rows that do not meet the criteria.

for you, it just so happened that the errors where in rows that did not meet the criteria.

Also, if the errors are in your CRITERIA range, sumif ignores them, because they don't meet the criteria
HOWEVER, if your errors are in the SUM range, And are in a row that meet the criteria, then sumif errors.
But if the errors are in sum range, in a row that does not meet the criteria, then no error.

Hope follow that, clear as mud right?