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.

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

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.

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

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

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

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.

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

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.

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

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.

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

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

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

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.

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

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.

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

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.

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

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.

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

Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

Your formula FAIL on my machine XL-2003 and XL-2007. I don't think it will
pass any version of XL. You can't FOOL me.

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


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Your formula FAIL on my machine XL-2003 and XL-2007. I don't think it will
pass any version of XL. You can't FOOL me.

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


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

Let me explain why your formula FAIL. Your MID function is trying to convert
text value to a real numeric value, eg. MID("SA4",2,255)+0 will return
#VALUE! error, because the second string is not the numeric value. It will
pass if the second string is a numeric value.

Try the following data from A1:A10

SXXX
S4B
S2
SAAA
S6
S9
S4
S4AX
S5Y
S7

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0)) --- Your formula return
#VALUE!

here is the correction

=SUM(IF((LEFT(A1:A10)="S")*(ISNUMBER(MID(A1:A10,2, 255)+0)),MID(A1:A10,2,255)+0)) ---- this formula returns 28



"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Your formula FAIL on my machine XL-2003 and XL-2007. I don't think it will
pass any version of XL. You can't FOOL me.

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


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Let me explain why your formula FAIL. Your MID function is trying to
convert
text value to a real numeric value, eg. MID("SA4",2,255)+0 will return
#VALUE! error, because the second string is not the numeric value. It will
pass if the second string is a numeric value.

Try the following data from A1:A10

SXXX
S4B
S2
SAAA
S6
S9
S4
S4AX
S5Y
S7

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0)) --- Your formula return
#VALUE!

here is the correction

=SUM(IF((LEFT(A1:A10)="S")*(ISNUMBER(MID(A1:A10,2, 255)+0)),MID(A1:A10,2,255)+0))
---- this formula returns 28



"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Your formula FAIL on my machine XL-2003 and XL-2007. I don't think it
will
pass any version of XL. You can't FOOL me.

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


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

You still don't get it do you. You are a "MS Excel MVP" you should know better.


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Let me explain why your formula FAIL. Your MID function is trying to
convert
text value to a real numeric value, eg. MID("SA4",2,255)+0 will return
#VALUE! error, because the second string is not the numeric value. It will
pass if the second string is a numeric value.

Try the following data from A1:A10

SXXX
S4B
S2
SAAA
S6
S9
S4
S4AX
S5Y
S7

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0)) --- Your formula return
#VALUE!

here is the correction

=SUM(IF((LEFT(A1:A10)="S")*(ISNUMBER(MID(A1:A10,2, 255)+0)),MID(A1:A10,2,255)+0))
---- this formula returns 28



"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Your formula FAIL on my machine XL-2003 and XL-2007. I don't think it
will
pass any version of XL. You can't FOOL me.

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


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

You still don't get it do you.

No, I get it. You're the one that doesn't get it.

Does this mean anything to you?

This worked perfectly!!!

What do you think that means?


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
You still don't get it do you. You are a "MS Excel MVP" you should know
better.


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let me explain why your formula FAIL. Your MID function is trying to
convert
text value to a real numeric value, eg. MID("SA4",2,255)+0 will return
#VALUE! error, because the second string is not the numeric value. It
will
pass if the second string is a numeric value.

Try the following data from A1:A10

SXXX
S4B
S2
SAAA
S6
S9
S4
S4AX
S5Y
S7

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0)) --- Your formula
return
#VALUE!

here is the correction

=SUM(IF((LEFT(A1:A10)="S")*(ISNUMBER(MID(A1:A10,2, 255)+0)),MID(A1:A10,2,255)+0))
---- this formula returns 28



"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Your formula FAIL on my machine XL-2003 and XL-2007. I don't think
it
will
pass any version of XL. You can't FOOL me.

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


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

You are either a "Smart Ass" or a "Dumb Ass"


Does this mean anything to you?

This worked perfectly!!!

What do you think that means?


"T. Valko" wrote:

You still don't get it do you.

No, I get it. You're the one that doesn't get it.

Does this mean anything to you?

This worked perfectly!!!

What do you think that means?


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
You still don't get it do you. You are a "MS Excel MVP" you should know
better.


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let me explain why your formula FAIL. Your MID function is trying to
convert
text value to a real numeric value, eg. MID("SA4",2,255)+0 will return
#VALUE! error, because the second string is not the numeric value. It
will
pass if the second string is a numeric value.

Try the following data from A1:A10

SXXX
S4B
S2
SAAA
S6
S9
S4
S4AX
S5Y
S7

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0)) --- Your formula
return
#VALUE!

here is the correction

=SUM(IF((LEFT(A1:A10)="S")*(ISNUMBER(MID(A1:A10,2, 255)+0)),MID(A1:A10,2,255)+0))
---- this formula returns 28



"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Your formula FAIL on my machine XL-2003 and XL-2007. I don't think
it
will
pass any version of XL. You can't FOOL me.

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


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9, A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

GFY

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
You are either a "Smart Ass" or a "Dumb Ass"


Does this mean anything to you?

This worked perfectly!!!

What do you think that means?


"T. Valko" wrote:

You still don't get it do you.

No, I get it. You're the one that doesn't get it.

Does this mean anything to you?

This worked perfectly!!!

What do you think that means?


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
You still don't get it do you. You are a "MS Excel MVP" you should know
better.


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Let me explain why your formula FAIL. Your MID function is trying to
convert
text value to a real numeric value, eg. MID("SA4",2,255)+0 will
return
#VALUE! error, because the second string is not the numeric value.
It
will
pass if the second string is a numeric value.

Try the following data from A1:A10

SXXX
S4B
S2
SAAA
S6
S9
S4
S4AX
S5Y
S7

=SUM(IF(LEFT(A1:A10)="S",MID(A1:A10,2,255)+0)) --- Your formula
return
#VALUE!

here is the correction

=SUM(IF((LEFT(A1:A10)="S")*(ISNUMBER(MID(A1:A10,2, 255)+0)),MID(A1:A10,2,255)+0))
---- this formula returns 28



"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in
message
...
Your formula FAIL on my machine XL-2003 and XL-2007. I don't
think
it
will
pass any version of XL. You can't FOOL me.

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


"T. Valko" wrote:

Your formula FAIL. It returns #VALUE!

Really?

This worked perfectly!!!


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote
in
message
...
Let's say,
A1= S2, A2=S3, A3=S4a, A5=S5, A6=S6, A7=S7, A8=S8, A9=S9,
A10=S10

Your forumula FAIL. It returns #VALUE!


"T. Valko" wrote:

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

Yes it is.

milked it

????????


--
Biff
Microsoft Excel MVP


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

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

Okay, if you want it spelt out.

Teethless mama made a point, which regardless of what the OP had said, was
valid. Your response was curt and to my eyes, petulant. TM's response was to
explain in greater detail, I think he felt you didn't understand what he was
saying. To which you made the same response. And so it went on, throgh
numerous iterations, with you responding with the same silly response rather
than a reasoned argument. TM then made an unwise statement, but your
response was out of line (IMO).

And ... I have seen you make the same sort of comment on other postings many
times.

--
---
HTH

Bob


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



"T. Valko" wrote in message
...
Yes it is.

milked it

????????


--
Biff
Microsoft Excel MVP


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

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

Well, you're entitled to your opinion/interpretation but I completely
disagree with it.

We'll just leave it at that.

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
Okay, if you want it spelt out.

Teethless mama made a point, which regardless of what the OP had said, was
valid. Your response was curt and to my eyes, petulant. TM's response was
to explain in greater detail, I think he felt you didn't understand what
he was saying. To which you made the same response. And so it went on,
throgh numerous iterations, with you responding with the same silly
response rather than a reasoned argument. TM then made an unwise
statement, but your response was out of line (IMO).

And ... I have seen you make the same sort of comment on other postings
many times.

--
---
HTH

Bob


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



"T. Valko" wrote in message
...
Yes it is.

milked it

????????


--
Biff
Microsoft Excel MVP


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

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

We'll just leave it at that.

Well, maybe just one important point:

responding with ... a reasoned argument.

The last time I tried that with greenteeth all I got for my efforts was to
be called a "whinning baby".

And another important point, which is what that exchange was all about:
greenteeth's suggested correction to my formula will also fail if......

The point being: almost every formula will fail, if..... At what point does
dealing with improbable/impossible "if's" become inefficient overkill?

I'm outta here!


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Well, you're entitled to your opinion/interpretation but I completely
disagree with it.

We'll just leave it at that.

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
Okay, if you want it spelt out.

Teethless mama made a point, which regardless of what the OP had said,
was valid. Your response was curt and to my eyes, petulant. TM's response
was to explain in greater detail, I think he felt you didn't understand
what he was saying. To which you made the same response. And so it went
on, throgh numerous iterations, with you responding with the same silly
response rather than a reasoned argument. TM then made an unwise
statement, but your response was out of line (IMO).

And ... I have seen you make the same sort of comment on other postings
many times.

--
---
HTH

Bob


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



"T. Valko" wrote in message
...
Yes it is.

milked it

????????


--
Biff
Microsoft Excel MVP


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

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

We'll just leave it that ... except when I want to make another point.

Calling him greenteeth is hardly grown-up is it?

Does being called a whining baby by some other unenlightened poster warrant
that silly level of response?

And finally, you make the response that could have been so effectively made
against TM's comments originally.

No I am out of here.

--
---
HTH

Bob


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



"T. Valko" wrote in message
...
We'll just leave it at that.

Well, maybe just one important point:

responding with ... a reasoned argument.

The last time I tried that with greenteeth all I got for my efforts was to
be called a "whinning baby".

And another important point, which is what that exchange was all about:
greenteeth's suggested correction to my formula will also fail if......

The point being: almost every formula will fail, if..... At what point
does dealing with improbable/impossible "if's" become inefficient
overkill?

I'm outta here!


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Well, you're entitled to your opinion/interpretation but I completely
disagree with it.

We'll just leave it at that.

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
Okay, if you want it spelt out.

Teethless mama made a point, which regardless of what the OP had said,
was valid. Your response was curt and to my eyes, petulant. TM's
response was to explain in greater detail, I think he felt you didn't
understand what he was saying. To which you made the same response. And
so it went on, throgh numerous iterations, with you responding with the
same silly response rather than a reasoned argument. TM then made an
unwise statement, but your response was out of line (IMO).

And ... I have seen you make the same sort of comment on other postings
many times.

--
---
HTH

Bob


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



"T. Valko" wrote in message
...
Yes it is.

milked it

????????


--
Biff
Microsoft Excel MVP


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