Sum If

  • Thread starter Thread starter orquidea
  • Start date Start date
O

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
 
This will count how may cells in A1:G1 have text beginning with 'v'
=SUMPRODUCT(--(LEFT(A1:G1)="v"))
best wishes
 
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
 
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?
 
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
 
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.
 
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?
 
Thanks, it worked

muddan madhu said:
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.
Click to expand...
 
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.
 
Back
Top