Troule with LEFT function 3

[inkserious]

I am trying to look down Column A and find all the cells whose value begins with the number two, and then sum the corresponding value in Column B. All of the values in Column A are three digit numbers. Below is the formula I am using; however, it returns a value of zero. C1 refers to a value of two. So if A1:A3 contained 201, 202 and 303 respectively, then the values in B1:B2 would be added together. B3 would be ignored as its first digit is three.

=SUMPRODUCT(--(LEFT(Sheet2!A:A,1)=C1),Sheet2!B:B)

Is it because the LEFT function only looks at text strings and it sees the values in A1:A3 as numeric strings?

Thanks for any help anyone can offer.

-ep

 

[Gavona]

I reckon you have the diagnosis right. Have a look at the Text function to convert those numbers into strings.

Gavin

 

[inkserious]

Thanks for your help Gavona. Here is the formula working correctly.

=SUMPRODUCT(--(LEFT(TEXT(Sheet2!A:A,"@"),1)=TEXT(C1,"@")),Sheet2!B:B)

-ep

 

[mintjulep]

Alternately, if you want to work with numbers rather than text use TRUNC(num/100,0) to return the first digit of a 3 digit number.

 

[SkipVought]

Hmmmm. LEFT? NUMBER?

faq68-6659

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

I'd be tempted to go with an array formula for this. Something like

{=SUM(IF(INT(A:A/100)=C1,B:B))}

 

[Gavona]

Taking Skip's point. This column probably should be text. To convert at source use Data,Text to columns and specify as Text.

Then the OP original solution would work.

Gavin

 

[strongm]

Still think that an array formula solution is more appropriate here ...

 

[Gavona]

Not clear that we have enough information to know what the most appropriate solution is.

The OP must have XL2007 or later because I just remembered that in earlier versions SumProduct cannot reference a whole column. So I re-tested the original formula in XL2003 and found that this works provided that C1 is text.
=SUMPRODUCT(--(LEFT(Sheet2!A1:A65535,1)=C1),Sheet2!B1:B65535)
Or if you don't want to worry about C1 then this will work;
=SUMPRODUCT(--(LEFT(Sheet2!A1:A65535,1)=TEXT(C1,"@")),Sheet2!B1:B65535)
Both the above work even if Column A is a mixture of numbers and text. So the original problem was that C1 was not entered as text, the format of values in column A appears not to matter.

Incidentally I could not get Strongm's solution to work for me. That's probably me because I haven't mastered array formulae. I used this entered as an array formulae (Ctrl-Shift-Enter) and yes it got braces.
{=SUM(IF(INT(A1:A65535/100)=C1,B:B))}
It would aid my understanding if you could help me to get the array solution to work, please.


All that said dynamic named ranges would be my choice. Calculation speed is noticeably affected by referencing the entire column. I expect that would apply to all solutions.


Gavin

 

[strongm]

>Not clear that we have enough information to know what the most appropriate solution is

I think I disagree. The SUMPRODUCT solution is clearly being used to emulate functionality already available in array formulae (possibly because the poster is not aware of what array formulae are or how they work), so why not use array formulae instead? The exact terms of the formula, however, are up for discussion (since, for example, my suggested solution won't deal with a mix of text and numbers in column A)

 

[Gavona]

(Some say that Sumproduct is an array formula.)
Whether Sumproduct or Array formula is I reckon a matter of user preference so long as it does the job. I don't suppose one is markedly slower to calculate than the other and any difference will be far less than the impact of referencing an entire column.

What we don't know is whether the source data is reliably numeric or if it is legitimately numeric and maths calculations are also done on the column, or if it is feasible / practical to convert the data to text (if appropriate) in the particular application.

Strongm, I see my silly mistake trying to implement your array formula in 2003. I should have used:
=SUM(IF(INT(A1:A65535/100)=C1,B1:B65535))
Thanks for posting it - it has helped me to get to start to get to grips with Array Formulae.

Gavin

 

[strongm]

SUMPRODUCT is having to do a LOT more work in this case to arrive at the result. So, whilst in most circumstances SUMPRODUCT is usually faster than an equivalet array formula, in this case it is slower ... (measured using Charles Williams RangeTimer method from

http://msdn.microsoft.com/en-us/library/aa730921.aspx#office2007excelperf_AdditionalResources)

Mind you - if, as you observed, the OP is using Excel 2007 then SUMIFS is dramaticaly quicker ... so something like:

=SUMIFS(B$1:B$65535,A$1:A$65535,">" & $C$1*100,A$1:A$65535,"<" & $C$1*100+100)

 

[Gavona]

How do you conclude "SUMPRODUCT is having to do a LOT more work in this case to arrive at the result." Clearly you have tested and it is - but you formed that view before testing and I can't see the rationale.

Array formula = 3 functions (Sum, IF, Int) and the divide operator plus the = operator

Sumproduct = 2 functions (if C1 is configured as text)(Sumproduct, Left) plus the = operator

That link is great - have a star!



Gavin

 

[strongm]

The SUMPRODUCT in this case (let's go with your idea that C1 is text)is having to do:


1) A string extraction (LEFT), which is slow
2) A boolean string comparison (essentially an IF), also slow
3) A conversion from boolean to numeric (--), because SUMPRODUCT requires numeric values
4) A multiplication of two numbers, whether match or not (this is the fudge kicking in to allow appearance of only summing of cells that match - we make the result of the non-match numerically equal to 0)
5) Sum the results of all the multiplications

Array function as given

1) Floating divide (/), one of Excels' fast operations
2) Conversion to integer (INT), again fast operation
3) boolean integer comparison
4) Sum only matching results

 

[Gavona]

Strongm's link to Improving Performance in Excel 2007 really is brilliant! I particularly like the "Sorted Data with Missing Values" section which gives technigues for improving the speed of lookups where you need an exact match.

IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
VLOOKUP(lookup_val, lookup_array, colnum, True), "notexist")

Also the "Looking Up a Set of Contiguous Rows or Columns" using an array formula.

If I could give another star then I would!




Gavin

 

[lionelhill]

Wow, that is the most useful document ever in the history of Excel. Does this site have a button for giving two stars at once?

 

[Gavona]

Yes, just click the link at the bottom of his post:
[red]*[/red] Thank strongm
for this valuable post!


Gavin

 

[cmeagan656]

A star for you strongm. That's a great link. Thanks!

Hope this helps.

Please help us help you. Read Tek-Tips posting polices before posting.
Canadian members check out Tek-Tips in Canada for socializing, networking, and anything non-technical.

 

[SkipVought]

Great stuff, strongm! ==>[purple]*[/purple]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}