I think you meant:

=SUMPRODUCT((B2:B100),--(LEFT(A2:A100,4)="2007"))



Dave F wrote:

Maybe this would work: =SUMPRODUCT(--(B2:B100,--LEFT(A2:A100,4)) ??
--
Brevity is the soul of wit.

"MLP" wrote:

Yes, I thought of this, too, but I was trying to avoid adding the helper
column as my spreadsheet is quite large as it is. Thanks for the help, I
might just have to add the extra column afterall.

"Dave F" wrote:

One option: create a helper column that extracts the four left characters
form column A, and then run SUMPRODUCT:

Helper column (col. C): =LEFT(A2,4) and fill down to the end of your range.

SUMPRODUCT: =SUMPRODUCT(--(B2:B100),(C2:C100="2007"))

Adjust the range to suit your needs.

Dave
--
Brevity is the soul of wit.


"MLP" wrote:

I'm trying to sum values based on searching for the partial match to the
first few letters/numbers within a range. For example:

A B
2007:A 15
2007:C 22
2006:D 33
2007:D 55
2005:8 100

I want to seach column A for all of the values that have "2007" regardless
of what follows the year, then sum the corresponding values in column B.

I think this is possible, but so far I haven't had any luck with the formula.
Thanks for your help!

--

Dave Peterson