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

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!

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

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!

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

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!

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

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!

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

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

"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!

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

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

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

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

"Dave Peterson" wrote:

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

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

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

Try this formula.

"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!

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

Yup that's what I meant.
--
Brevity is the soul of wit.


"Dave Peterson" wrote:

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

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

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,D4:D24)

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