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