Perhaps this feature is not supported in Excel but here goes.... I have data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single cell
would show S5. What I am attempting to do is 1. Identify which cells in a
single column have an S, 2. Once identified, add the value next to S to the
sum of all Sick time. To get the count of S I used Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

This worked perfectly!!! Thank you very much!!! Also, thanks for the
reminder to use Ctrl-Shift-Enter for the Array entry, I had missed that step
in my previous attempts.

-Mac

"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"M.A. Clark" wrote in message
...
This worked perfectly!!! Thank you very much!!! Also, thanks for the
reminder to use Ctrl-Shift-Enter for the Array entry, I had missed that
step
in my previous attempts.

-Mac

"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I
have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells
in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used
Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

Your formula FAIL. It returns #VALUE! error when any cells contain certain
text. (eg. S4a)


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"M.A. Clark" wrote in message
...
This worked perfectly!!! Thank you very much!!! Also, thanks for the
reminder to use Ctrl-Shift-Enter for the Array entry, I had missed that
step
in my previous attempts.

-Mac

"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I
have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells
in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used
Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Your formula FAIL. It returns #VALUE! error when any cells contain certain
text. (eg. S4a)


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"M.A. Clark" wrote in message
...
This worked perfectly!!! Thank you very much!!! Also, thanks for the
reminder to use Ctrl-Shift-Enter for the Array entry, I had missed that
step
in my previous attempts.

-Mac

"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I
have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a
single
cell
would show S5. What I am attempting to do is 1. Identify which
cells
in a
single column have an S, 2. Once identified, add the value next to S
to
the
sum of all Sick time. To get the count of S I used
Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data
to
multiple cells is a bit of a fight. Any help would be appreciated.

Sorry for all the guff you took on-line over your helping me with this
issue, it really did fix the problem and is sincerely appreciated.

-Mac


"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

Don't you think he milked it?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"M.A. Clark" wrote in message
...

Sorry for all the guff you took on-line over your helping me with this
issue, it really did fix the problem and is sincerely appreciated.

-Mac


"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I
have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells
in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used
Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

How so?

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
Don't you think he milked it?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"M.A. Clark" wrote in message
...

Sorry for all the guff you took on-line over your helping me with this
issue, it really did fix the problem and is sincerely appreciated.

-Mac


"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I
have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a
single
cell
would show S5. What I am attempting to do is 1. Identify which cells
in a
single column have an S, 2. Once identified, add the value next to S
to
the
sum of all Sick time. To get the count of S I used
Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

Oh come, that is not a serious question.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"T. Valko" wrote in message
...
How so?

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
Don't you think he milked it?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"M.A. Clark" wrote in message
...

Sorry for all the guff you took on-line over your helping me with this
issue, it really did fix the problem and is sincerely appreciated.

-Mac


"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I
have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a
single
cell
would show S5. What I am attempting to do is 1. Identify which cells
in a
single column have an S, 2. Once identified, add the value next to S
to
the
sum of all Sick time. To get the count of S I used
Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data
to
multiple cells is a bit of a fight. Any help would be appreciated.

No guff taken! People who live in glass houses should know not to throw
rocks!

Thanks for the feedback!


--
Biff
Microsoft Excel MVP


"M.A. Clark" wrote in message
...

Sorry for all the guff you took on-line over your helping me with this
issue, it really did fix the problem and is sincerely appreciated.

-Mac


"T. Valko" wrote:

One way:

Try this array formula**:

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I
have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells
in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used
Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

=SUM(IF(B5:B370<"",IF(LEFT(B5:B370,1)="S",--(RIGHT(B5:B370,LEN(B5:B370)-1)))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

Thank you, this also worked very well and I was able to learn more about
nesting commands. Sincerely appreciate the help!!!

-MAC

"Bob Phillips" wrote:

=SUM(IF(B5:B370<"",IF(LEFT(B5:B370,1)="S",--(RIGHT(B5:B370,LEN(B5:B370)-1)))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"M.A. Clark" <M.A. wrote in message
...
Perhaps this feature is not supported in Excel but here goes.... I have
data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

=SUMPRODUCT((LEFT(A1:A10)="S")*MID(A1:A10,2,99))

Does required ctrl+shift+enter, Just ENTER


"M.A. Clark" wrote:

Perhaps this feature is not supported in Excel but here goes.... I have data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single cell
would show S5. What I am attempting to do is 1. Identify which cells in a
single column have an S, 2. Once identified, add the value next to S to the
sum of all Sick time. To get the count of S I used Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

I meant Doesn't required ctrl+shift+enter, Just ENTER


"Teethless mama" wrote:

=SUMPRODUCT((LEFT(A1:A10)="S")*MID(A1:A10,2,99))

Does required ctrl+shift+enter, Just ENTER


"M.A. Clark" wrote:

Perhaps this feature is not supported in Excel but here goes.... I have data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single cell
would show S5. What I am attempting to do is 1. Identify which cells in a
single column have an S, 2. Once identified, add the value next to S to the
sum of all Sick time. To get the count of S I used Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.

Your formula "fail". It returns #VALUE! error when any cells contain only
text or are empty.


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
I meant Doesn't required ctrl+shift+enter, Just ENTER


"Teethless mama" wrote:

=SUMPRODUCT((LEFT(A1:A10)="S")*MID(A1:A10,2,99))

Does required ctrl+shift+enter, Just ENTER


"M.A. Clark" wrote:

Perhaps this feature is not supported in Excel but here goes.... I
have data
that represents Sick Time, Holiday Time, etc in single cells on a
spreadsheet. For example, to represent a Sick day of 5 hours, a single
cell
would show S5. What I am attempting to do is 1. Identify which cells
in a
single column have an S, 2. Once identified, add the value next to S to
the
sum of all Sick time. To get the count of S I used
Countif(B5:B370,"S*")
This works. But I have tried all kinds of combinations to Sum the
resulting
numbers. This is a group common spreadsheet so separating the data to
multiple cells is a bit of a fight. Any help would be appreciated.