| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
