Using SUMIF and FIND/RIGHT to add values of a partial cell referen

[Guest]

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 33
2007 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!

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Maybe this would work: =SUMPRODUCT(--(B2:B100,--LEFT(A2:A100,4)) ??

 

[Guest]

No luck. I'll just move on with the helper column. Thanks again.

 

[Dave Peterson]

I think you meant:

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

 

[Guest]

That worked! Thanks for the help, now I can move on.

 

[Guest]

=SUMIF(A1:A5,"2007*",B1:B5)

Try this formula.

 

[Guest]

Yup that's what I meant.

 

[Guest]

I suggest not mixing your criteria. Always keep them seperate and you will
find your formulas much easier to make and use.
This demo may be what you want. It finds a name then counts the number of
times it shows up, then adds up the number associated with that name.

name number
Danny 10
John 20
Sam 30
Debby 40
Bev 50
Cort 60


Danny 100
John 200
Sam 300
Christina 400
Bev 500
Cort 600

Danny 1000
John 2000
Sam 3000
Christina 4000
Danny 5000
Cort 6000

Danny 6110 (C26 put: =SUMIF(B4:B24,B26,D424)

Count 4 (C28 put: =COUNTIF(B4:B24,B26)