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

[M.A. Clark]

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.

 

[T. Valko]

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)

 

[Bob Phillips]

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

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

 

[M.A. Clark]

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

-MAC

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Teethless mama]

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

Does required ctrl+shift+enter, Just ENTER

 

[Teethless mama]

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

 

[T. Valko]

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

 

[Teethless mama]

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

 

[T. Valko]

Your formula FAIL. It returns #VALUE!

Really?

 

[Teethless mama]

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]

Your formula FAIL. It returns #VALUE!

Really?

 

[Teethless mama]

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]

Your formula FAIL. It returns #VALUE!

Really?

 

[Teethless mama]

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]

Your formula FAIL. It returns #VALUE!

Really?

 

[Teethless mama]

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

 

[T. Valko]

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?

What do you think that means?

 

[Teethless mama]

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

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

Does this mean anything to you?


What do you think that means?