Sum If

[orquidea]

Hi:

I have a calendar in a spreadsheet. I record by week the vacations days and
sick days. In cell A I would like to get the sum of all the cells that start
with "v". In the example the total is 4. How could I write a formula which
calculate this?
Could anyone help me please?
JANUARY
Cell A 1 - 7 8 - 14 15 - 21
Total V Cell C5 Cell D5 Cell E5
4 v3 s2 v1
Thanks,
Orquidea

 

[Bernard Liengme]

This will count how may cells in A1:G1 have text beginning with 'v'
=SUMPRODUCT(--(LEFT(A1:G1)="v"))
best wishes

 

[orquidea]

Thanks for your answer but I want to add the values, the result should be 4
(v3, V1)

 

[muddan madhu]

try this ( use ctrl + shift + enter )

=SUM(--(IF(LEFT(C2:E2)="v",RIGHT(C2:E2,1))))

 

[Bernard Liengme]

If all cells have entries in the form LetterDigit(s):
a) if the number is always a single digit:
=SUMPRODUCT(--(LEFT(A1:G1)="v"),--RIGHT(A1:G1,1))
b) if the number can be greater than 9
=SUMPRODUCT(--(LEFT(A1:G1)="v"),--MID(A1:G1,2,255))
I will try to find for the case when some cells are empty or contain only
text
best wishes

 

[orquidea]

Thanks for your answer. Please tell me when do I hit CRTL + SHIF + ENTER. I
typed the formula but I get "#Value" error. Do you have any idea what I am
doing wrong?

 

[Bernard Liengme]

Cannot find an easy one-cell solution when some cells are empty.
Use a helper row with formula
=IF(LEFT(A1)="v",--RIGHT(A1,1),0)
or
=IF(LEFT(A1)="v",--MID(A1,2,255),0)
and sum the individual values

 

[muddan madhu]

copy the formula and paste it in your worksheet now hit F2 or double
click on the cell | you will see the formula in the cell , now hit
ctrl + shift + enter
you will see the curley brackets at the begining & at the end of the
formula.

 

[orquidea]

Beranrd:

For some reason I keep getting an error when typing the formula below. I
already made sure I have all the parenthesis. Do you have any idea what I am
doing wrong?

 

[Bernard Liengme]

Please send me the file (to me not the newsgroup!)
best wishes

 

[orquidea]

Thanks, it worked

copy the formula and paste it in your worksheet now hit F2 or double
click on the cell | you will see the formula in the cell , now hit
ctrl + shift + enter
you will see the curley brackets at the begining & at the end of the
formula.

 

[orquidea]

Bernard

I don't know how to send a file to a person on this web. Anyways, I
figured it out. Thanks for your help.