• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sumproduct returns zero but F9 calculates to a different result

Sajan

Sajan

Excel Ninja
Hi,

I have the following dataset (simplified to illustrate my question):

[pre]
Code: 
#  A       B  C  D  E
1  Item 1  1  2     Item 2
2  Item 2  Q  4     Item 4
3  Item 3  5  6
4  Item 4  7  8
Column A has a list of text values (Item 1, Item 2, etc.)

Column B and C has the mostly numeric values that I need to add up.  Please note that cell B2 has the non-numeric value of "Q".

Column E has the criteria (also a list of text values)


I am attempting to sum the values in columns B and C, when the value in Column A is one of the values in Column E.


I am using the following formula:

=sumproduct ( not(iserror(match(A1:A4, E1:E2, 0))) * iferror(B1:C4*1,0) )
[/pre]
In this case, I am expecting the value 4+7+8=19. However, Excel returns 0 (zero).

If I select the formula and press F9, I get the correct result of 19.


Any ideas on what I am overlooking?


Thanks,

Sajan.
 
I think we can get it to work by using the TEXT function.

=SUMPRODUCT(ISNUMBER(MATCH(A1:A4,E1:E2,0))*TEXT(B1:C4,"#;#;#;""0"""))


It's forcing all the text values to become 0's, and the formula can then evaluate correctly.
 
Thanks Luke M. That is awesome. I had not seen such a clever usage of the number format, especially, the seldom used last parameter!! Thank you very much!

-Sajan.
 
Hi Narayan,


Thanks for that clue! I had overlooked that one.


I have typically avoided array formulas (entered using Ctrl Shift Enter) because my worksheets have several hundred cells with similar sumproduct calculations, and the "array" would need to be repeated for each individual cell. (The conditions evaluated in each cell differ by rows and columns.)


(I have, perhaps wrongly, assumed that the array formulas have a performance penalty.)


By the way, any ideas on why the original formula worked with Ctrl+Shift+Enter?


Thanks,

Sajan.
 
Hi Sajan ,


You are right that array formulae have a performance penalty associated with them , but given Excel's limits of thousands of columns and a million rows , assuming that you will hit the barrier at 20 % of these limits , several hundred cells should not impact on performance in any way.


You need to look at your workbook to see if there are so many rows / columns / references to other worksheets / external workbooks involved before you decide that array formulae are to be avoided.


Two points to be considered in favour of / against array formulae are :


1. Can you use helper columns ? If yes , then you can probably do without array formulae.


2. Is your design absolutely fool-proof and tested ? Standard formulae are easier to test and debug ; array formulae involving thousands of rows and data which is not standardized and several criteria can be a pain to troubleshoot.


Coming back to your other question , using F2 and F9 is not a fool-proof way of evaluating cell formulae ; the best way is to use the Evaluate Formula selection under Formula Auditing , which will step through the whole process. When you do this using the ENTER and the CTRL SHIFT ENTER methods , you will see the difference ; when you use ENTER alone , the segment :

[pre]
Code: 
IFERROR(B1:C4*1,0)
returns only a #VALUE! error.


When used with the CTRL SHIFT ENTER option , the same segment returns :

IFERROR({1,2;#VALUE!,4;5,6;7,8},0)
[/pre]
where the values within the range B1:C4 are being evaluated individually , and an array of results is returned. The semi-colons indicate the row separation i.e. the values 1 and 2 are in the first row of the array , the values #VALUE! and 4 are in the second row ... Once the IFERROR wrapper is applied to this array , you get {1,2;0,4;5,6;7,8} which is what you want.


Narayan
 
Hi Sajan ,


If you are interested , go through these links :


1. http://msdn.microsoft.com/en-us/library/aa730921%28office.12%29.aspx


2. http://msdn.microsoft.com/en-us/library/ff700515.aspx


3. http://msdn.microsoft.com/en-us/library/ff700514.aspx


4. http://msdn.microsoft.com/en-us/library/ff726673.aspx


Narayan
 
You are right that array formulae have a performance penalty associated with them
Click to expand...

SUMPRODUCT is an array formula, it just is not array-entered, so the performance difference between SP and an array-entered SP is minimal.


But, if you array-enter it, you might just as well not bother with SP


=SUM(NOT(ISERROR(MATCH(A1:A4,E1:E4,0)))*IFERROR(A1:C4*1,0))
 
Thanks Narayan for the detailed explanation and the links to the info about optimizing calculations. That was great info. I appreciate it very much. I am going to assess my worksheets to see how I can incorporate this insight to improve performance.


Thanks xld for your comments.


-Sajan.
 
Hello Gents,


I know this is an old thread, but using TEXT(B1:C4,"#;#;#;""0""") remind somethings.


If you have any zero values in B1:C4, will return error. You can avoid this replacing third # to 0.


If there is any decimal values will make it ROUND. eg: 1.2 will make as 1, 1.5 will make as 2. 0.4 will make as 0, 0.5 will make as 1, so you wouldn't get the proper SUM.


I would recommend multiply first array with number of columns, then next array SEPARATED by 'comma'


=SUMPRODUCT(ISNUMBER(MATCH(A1:A4,E1:E2,0))*COLUMN(B1:C1)^0,B1:C4)
 
Hi Haseeb,

Your suggestion is brilliant! Thank you!

The idea of converting the left matrix to be the same size as the right matrix helps the formula to use the "normal" SUMPRODUCT() capability to work around text embedded in numbers.

This approach may also be faster than the text conversion. (I have not timed the new formula scientifically, but it "feels" faster!)


(I tweaked the formula slightly for my usage, changing column(B1:C1)^0 to column(B1:C4)^0 so that I can copy / paste the same range without too much thought!!)


thanks!


-Sajan.
 
You must log in or register to reply here.
Back
Top