Sumif / Countif - Not certain - want to extract data from cell and

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.

=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.

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.

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.

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.

=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.

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.