Trying to add numeric values in range

Hello All,

In A1:Z1 I have a letter and a number in each cell, e.g. (C4, V5, B8, etc).
In AA:1 I need a formula that would the add the number(s) in A1:Z1 that match a certain letter, in this example the letter V. If V3, V6, V1 appear in the range of A1:Z1 it should return a value of 10.

I tired the =SUMIF(A1:Z1,"=V",A1:Z1) but it returns a zero.

Any help would be appreciated, thanks.

Mike

You are on the right track. It depends on the structure of the data in your cells. Is the letter always a single character. if so, you can break apart the letter from the number like this. It would be easier to do this in a few steps rather than 1 formula

cell A2 enter
=left(A1,1)
will give you the letter

cell A3, enter
=right(A1,len(A1)-1)
will give you the number

You can then do your sumif, summing the third row based on the condition in the second row.

Matt

Hi Matt,

After doing your 2 step formula, the =SUMIF doesn't work. I noticed that in the 2nd part, the numbers are on the left side of the cell, maybe the =SUMIF formula thinks that they are text.

Also I just realized that some of the numbers may be a decimal but the highest number would be an eight.

Mike

Yes, you are right. It will show the numbers as text. Just put a =value() statement around it

=VALUE(right(A1,len(A1)-1))

Matt

Thanks Matt,

got it working.

Mike