If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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. |
#23
|
|||
|
|||
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. |
#24
|
|||
|
|||
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. |
#25
|
|||
|
|||
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. |
#26
|
|||
|
|||
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. |
#27
|
|||
|
|||
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. |
#28
|
|||
|
|||
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. |
#29
|
|||
|
|||
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. |
#30
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|