Sum If
 

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

Sum If
 

This will count how may cells in A1:G1 have text beginning with 'v'
=SUMPRODUCT(--(LEFT(A1:G1)="v"))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"orquidea" wrote in message
...
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

Sum If
 

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


"orquidea" wrote:

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

Sum If
 

try this ( use ctrl + shift + enter )

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

On Nov 18, 10:56*pm, orquidea
wrote:
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

Sum If
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"orquidea" wrote in message
...
Thanks for your answer but I want to add the values, the result should be
4
(v3, V1)


"orquidea" wrote:

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

Sum If
 

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?

"muddan madhu" wrote:

try this ( use ctrl + shift + enter )

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

On Nov 18, 10:56 pm, orquidea
wrote:
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

Sum If
 

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"orquidea" wrote in message
...
Thanks for your answer but I want to add the values, the result should be
4
(v3, V1)


"orquidea" wrote:

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

Sum If
 

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.



On Nov 19, 12:06*am, orquidea
wrote:
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?

"muddan madhu" wrote:
try this ( use ctrl + shift + enter )

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

On Nov 18, 10:56 pm, orquidea
wrote:
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

Sum If
 

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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"orquidea" wrote in message
...
Thanks for your answer but I want to add the values, the result should be
4
(v3, V1)


"orquidea" wrote:

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

Sum If
 

Please send me the file (to me not the newsgroup!)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"orquidea" wrote in message
...
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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"orquidea" wrote in message
...
Thanks for your answer but I want to add the values, the result should
be
4
(v3, V1)


"orquidea" wrote:

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

Sum If
 

Thanks, it worked

"muddan madhu" wrote:

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.



On Nov 19, 12:06 am, orquidea
wrote:
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?

"muddan madhu" wrote:
try this ( use ctrl + shift + enter )

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

On Nov 18, 10:56 pm, orquidea
wrote:
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

Sum If
 

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.

"Bernard Liengme" wrote:

Please send me the file (to me not the newsgroup!)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"orquidea" wrote in message
...
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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"orquidea" wrote in message
...
Thanks for your answer but I want to add the values, the result should
be
4
(v3, V1)


"orquidea" wrote:

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