Sumif with a twist?

[Bruce]

43975000 50
43865000 100
43828000 5
43828001 21

Assume the block of data above, and that I want to do a sumif formula, with
the range being the 1st column, but only the first 5 characters of said cells
in the range. Example, the criteria will be 43828 and I want the result to be
the value 26. The reason is this is a basically a rollup of values in column
B when the match is on the first 5 characters.

Does this make sense?

Thanks,

[Aladin Akyurek]

If the figures in A2:A10 are true numbers and F2 houses the number 43828:

=SUMPRODUCT(--(LEFT($A$2:$A$10&"00000",5)+0=F2),$B$2:$B$10)

If the figures in A2:A10 are text-formatted number and F2 as before:

=SUMIF($A$2:$A$10,F2&"*",$B$2:$B$10)

Bruce wrote:
> 43975000 50
> 43865000 100
> 43828000 5
> 43828001 21
>
> Assume the block of data above, and that I want to do a sumif formula, with
> the range being the 1st column, but only the first 5 characters of said cells
> in the range. Example, the criteria will be 43828 and I want the result to be
> the value 26. The reason is this is a basically a rollup of values in column
> B when the match is on the first 5 characters.
>
> Does this make sense?
>
> Thanks,

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.