Solved How to separate cells into categories

Dell / Inspiron 17r
June 28, 2020 at 07:53:02
Specs: windows 10, 2.3/8
I have a row of cells (n = 31) that contains either B:10 or T:10. I want to count the frequency of cells that have either B:10 or T:10. as well as sum the totals for each respective group. I tried using =COUNTA and =SUM but that didn't separate the groups.

Thank you.
Brian W


See More: How to separate cells into categories

Reply ↓  Report •

✔ Best Answer
June 29, 2020 at 19:11:04
With this data, the following formulas return the values you posted in #8.

        A        B         C         D         E         F       G
3      T:10     B:4.5     T:6.2     T:6.2     B:4.5     B:0     T:6.2

A1: =COUNTIF(A3:AE3,"T*")
Result: 4

B1: =SUM(IF(ISNUMBER(FIND("T",A3:AE3)),VALUE(RIGHT(A3:AE3,LEN(A3:AE3)-2)),0)) - Array entered via Ctrl-Shift-Enter
Result: 28.6

A2: =COUNTIF(A3:AE3,"B*")
Result: 3

B2: =SUM(IF(ISNUMBER(FIND("B",A3:AE3)),VALUE(RIGHT(A3:AE3,LEN(A3:AE3)-2)),0)) - Array entered via Ctrl-Shift-Enter
Result: 9

message edited by DerbyDad03



#1
June 28, 2020 at 17:47:01
What do you mean by "count the frequency of cells"? There is a FREQUENCY function in Excel, but the description of your data doesn't fit what that function is used for.

I also don't know what you mean when you say COUNTA and SUM "didn't separate the groups". What do you mean by "separate cells into categories"?

Please clarify what you are trying to do.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
June 28, 2020 at 17:58:59
DerbyDad03,

Thank you for your response. In one row, I have cells that contain either B:10 or T:10. I want to find out how many cells have B:10 and put that response in A1 and how many cells have T:10 and put that response in A2. Additionally, I want to find out the sum total of 10s In the “B” cells and put that sum in B1 and put the sum total of 10s in the “T” cells in B2.

Thanks again,
Brian W


Reply ↓  Report •

#3
June 28, 2020 at 18:43:53
Assuming A3:AE3 (n=31)

A1: =COUNTIF(A3:AE3,"=B:10")

B1: =A1*10

There is no "separation of cells", just "counting of values".


message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
June 29, 2020 at 05:40:52
DerbyDad03,

Thank you for your response. It worked as I had intended. How would these formulae change if the B and the T numbers were not constant with B and T both having a range of 0.0 to 15.0?

Thanks again,
Brian W


Reply ↓  Report •

#5
June 29, 2020 at 06:49:42
Once again, I don't understand the question. Since I only provided the formula for B:10 and you didn't ask about T:10, I assume that you know how to change the formula to work for T:10.

I don't know anything about your work process, so I can only offer this:

If you understand how the formulae work for B:10 and T:10, you should know how to change the formulae to work for any value. If you can't get them working, please provide some more information as to what the issue is.

message edited by DerbyDad03


Reply ↓  Report •

#6
June 29, 2020 at 07:45:59
DerbyDad03,

I was able to change the formula so that T:10 worked correctly. My issue now regards using numbers other than 10, e, g, B:4.5, T:6.2, etc. The range of T and B values is from 0.0 to 15.0. I still would need how many times T or B is used and their respective totals.

I tried various renditions using =Countif but none of them worked.

Thanks,
Brian W


Reply ↓  Report •

#7
June 29, 2020 at 08:37:59
With this data, the following formulas work just fine for me:

        A        B         C         D         E         F       G
3      T:10     B:4.5     T:6.2     T:6.2     B:4.5     B:0     T:6.2


A1: =COUNTIF(A3:AE3,"=B:4.5") (Result: 2)
B1: =A1*4.5 (Result: 9)

A2: =COUNTIF(A3:AE3,"=T:6.2") (Result: 3)
B2: =A2*6.2 (Result: 18.6)


If those are not the results you are looking for, you'll need to explain what you would expect, based on my sample data set.

message edited by DerbyDad03


Reply ↓  Report •

#8
June 29, 2020 at 09:04:52
DerbyDad03,

Based on your data set, I need:

A1 <all Ts> 4 (T:10, T:6.2, T:6.2, T:6.2)
B1 <total of Ts> 28.6 (10+6.2+6.2+6.2)

A2 <all Bs> 3 (B:4.5, B:4.5, B:0)
B2 <total of Bs> 9.0 (4.5+4.5+0)

The value/numbers in parentheses are shown only for illustrative purpose and are not needed in final version.

Thanks again,
Brian W


Reply ↓  Report •

#9
June 29, 2020 at 09:35:14
Brian,

In the future, please try to include all of the details related to your requirements in your original post.

In your OP (twice) and again in # 3, you said that your cells "contain either B:10 or T:10". You also said that you wanted to sum the "10's".

What you tell us is all that we have to work with. I understand that you might be trying to keep it simple and that you might want to try and figure out the complicated parts on your own, but when the information that you provide is so different than what you actually have/need, you cause us to waste time working on and testing solutions that will never get you where you need to be. 99% of the time we don't just post solutions, we actually set up test worksheets and make sure that our suggestions will meet the requirements given. In this case, it wasn't until post #8 that we were finally given the full set of requirements.

This is what you OP should have looked like.

"I have a row of cells (n = 31) that start with either B: or T:. Each letter is then followed by a value that ranges from 0.0 to 15.0. I have 2 requirements:

1 - Count the number of cells that begin with each letter.
2 - SUM the values that follow each letter.

For example, let's say I have the following data set:

        A        B         C         D         E         F       G
3      T:10     B:4.5     T:6.2     T:6.2     B:4.5     B:0     T:6.2

The results should be:

       A          B
1      4         28.6         (for T)
2      3          9           (for B) 

Thanks for your help."

I will work on a solution to meet this set of requirements.


message edited by DerbyDad03


Reply ↓  Report •

#10
June 29, 2020 at 11:05:12
DerbyDad03,

I am sorry that I did not articulate my requirements in a more concise manner.

Thanks again for your assistance and I look forward to seeing your solution.

Brian W


Reply ↓  Report •

#11
June 29, 2020 at 19:11:04
✔ Best Answer
With this data, the following formulas return the values you posted in #8.

        A        B         C         D         E         F       G
3      T:10     B:4.5     T:6.2     T:6.2     B:4.5     B:0     T:6.2

A1: =COUNTIF(A3:AE3,"T*")
Result: 4

B1: =SUM(IF(ISNUMBER(FIND("T",A3:AE3)),VALUE(RIGHT(A3:AE3,LEN(A3:AE3)-2)),0)) - Array entered via Ctrl-Shift-Enter
Result: 28.6

A2: =COUNTIF(A3:AE3,"B*")
Result: 3

B2: =SUM(IF(ISNUMBER(FIND("B",A3:AE3)),VALUE(RIGHT(A3:AE3,LEN(A3:AE3)-2)),0)) - Array entered via Ctrl-Shift-Enter
Result: 9

message edited by DerbyDad03


Reply ↓  Report •

#12
June 30, 2020 at 06:02:48
DerbyDad03,

Thank you for helping me with this issue. Please explain “Array entered via Ctrl-Shift-Enter” as I am unfamiliar with this statement.

Thanks again,
Brian W


Reply ↓  Report •

#13
June 30, 2020 at 06:38:49
If you do a Google search for Excel Array Formulas you'll find more information than anyone could possible absorb.

Basically, an array formula is a formula that can perform multiple calculations on one or more items in an array. An array is a row or column of values, or a combination of rows and columns of values.

In your case, the array formulas that I offered will perform the multiple calculations on the array of cells A3:AE3. Specifically, it will perform the following steps on a cell by cell basis:

1 - IF(ISNUMBER(FIND("B",A3:AE3))

Determine if a "B" is found in the cell.

2 - VALUE(RIGHT(A3:AE3,LEN(A3:AE3)-2)),0

If a B is found, extract the Value that is found on the Right of that cell. If B is not found, return 0.

Again, it will perform these steps on a cell-by-cell basis. It's similar to a For-Next loop in programming.

3 - SUM

Once all of the appropriate values (based on the IF function) are extracted, the formula will perform the SUM operation on them.

In short, for the A3:G3 data set shown above, the array formula for B will eventually reduce to this:

=SUM{0,4.5,0,0,4.5,0,0,0,0,0,0,0,0 etc.}

You can watch these steps by using the Evaluate Formula feature on the Formula tool bar.

In order to tell Excel that the formula is an Array formula and that it needs to do multiple calculations on the array, you have to enter the formula by pressing Ctrl-Shift-Enter. This will place braces { } around the formula, indicating that it is an array formula. You can not just type the braces around the formula, you must use Ctrl-Shift-Enter and have Excel add them to the formula. The formula will look like this once you've done that:

{=SUM(IF(ISNUMBER(FIND("B",A3:AE3)),VALUE(RIGHT(A3:AE3,LEN(A3:AE3)-2)),0))}

Any time that you edit the formula in the formula bar, the braces will go away and you'll need to use Ctrl-Shift-Enter again to get the braces back. Without those braces, Excel will not perform the cell-by-cell operations and you will not get the results you are looking for.

message edited by DerbyDad03


Reply ↓  Report •

#14
June 30, 2020 at 10:09:04
> In order to tell Excel that the formula is an Array formula and
> that it needs to do multiple calculations on the array, you have
> to enter the formula by pressing Ctrl-Shift-Enter. This will place
> braces { } around the formula, indicating that it is an array
> formula. You can not just type the braces around the formula,
> you must use Ctrl-Shift-Enter and have Excel add them to the
> formula.

That is a very odd way of doing things, unlike anything I've ever
dealt with. Are there other operations that work similarly?

-- Jeff, in Minneapolis


Reply ↓  Report •

#15
June 30, 2020 at 11:52:56
Jeff,

re: "Are there other operations that work similarly?"

I'm not sure what you mean by that question.

Do you mean...

"Are there other operations that require multiple keys to perform an action?"

... or do you mean:

"Are there other operations that perform array-compatible calculations?"

Either way, the answer is essentially "Yes" :-)

message edited by DerbyDad03


Reply ↓  Report •

#16
July 3, 2020 at 07:05:01
DerbyDad03,

I entered the formula and then pressed Ctrl-Shift-Enter but the braces did not appear. What should I do to correct this situation?

Thank you.
Brian W


Reply ↓  Report •

#17
July 3, 2020 at 07:59:13
Brian,

It can be a bit tricky if you have never done it before,
The sequence is:

Press and Hold the CTRL key
While you
Press and Hold the Shift key
While you
Pres the Enter Key

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#18
July 3, 2020 at 11:59:19
What Mike said plus the cursor must be in the formula bar, not just in the cell.

(Just making sure all bases are covered.)


message edited by DerbyDad03


Reply ↓  Report •

#19
July 3, 2020 at 15:33:22
mmcconaghy,

Thank you for your assistance. I followed your directions but unfortunately the braces did not appear. Is there an Excel setting that needs to be looked at?

Thanks again,

Brian W


Reply ↓  Report •

#20
July 3, 2020 at 15:34:54
DerbyDad03,

Thank you for your assistance. I followed mmcconaghy’ s directions and yours and the braces did not appear.

Thanks again,
Brian W


Reply ↓  Report •

#21
July 3, 2020 at 20:05:10
Are you using Excel Online?

The following can be found near the bottom of this page:

https://support.microsoft.com/en-us...

Excel for the web doesn’t support creating array formulas. You can view the results of array formulas created in Excel desktop application, but you can’t edit or recalculate them. If you have the Excel desktop application, click Open in Excel to work with arrays.

If that is your issue, then my only other suggestion is to extract the numerical values using a text function, then sum those values. You'll need to multiply the extracted text by 1 (or add 0, etc.) to turn the text into numbers before you can sum them.

Create a range with this formula for T and a similar one for B, then sum that range:

=IF(LEFT(A3)="T",RIGHT(A3,LEN(A3)-2)*1,"")

message edited by DerbyDad03


Reply ↓  Report •

#22
July 4, 2020 at 06:54:26
Brian's difficulty in putting braces around a formula, and the fact
that the online version of Excel can't create or edit array formulas,
appears to illustrate why that keypress thing is so strange.

I know nothing about programming for Excel, but I do know a bit
about programming in general. If you told me that in some version
of BASIC, in order to type in a formula for an array, I had to press
a combination of keys instead of just typing braces, I would respond
by telling you that that is insane. It makes no sense. It is crazy.
And please tell me why anyone would design it that way.

I suppose there must be a reason for it, but it sounds ridiculous.

My question was: Are there other operations-- besides indicating
that a formula is for an array-- that require an arcane, hidden,
and essentially secret keypress combination rather than simply
pressing the keys for the characters displayed?

In this case, I just press the shift key and the two bracket keys to
type { and } into this texbox. Nothing hidden. Why should it be so
very different in Excel?

-- Jeff, in Minneapolis

message edited by Jeff Root


Reply ↓  Report •

#23
July 4, 2020 at 13:46:40
re: "I would respond by telling you that that is insane. It makes no sense. It is crazy."

Well, I guess you feel pretty strongly about the matter.

re: And please tell me why anyone would design it that way.

It's quite simple: The only way that Excel recognizes that a formula has been entered in the formula bar is because it begins with an equal sign. No equal sign? Then it's either a text string, a number or a date/time. If the user types {=SUM(A1:A5)} and presses Enter, that exact text string will appear in the cell. It's not a formula. By requiring Ctrl-Shift-Enter, the user is telling Excel that he wants =SUM(A1:A5) to be converted to an Array formula. The braces then let the user (and anyone else that comes along later) know that it's an array formula and must be treated as such in order to work properly.

re: "that require an arcane, hidden, and essentially secret keypress combination"

I think you are being a bit redundant there, but I guess this has put a bee in your proverbial bonnet.

There is nothing "arcane, hidden or essentially secret" about it. It's well documented and has been for years. You'd be hard-pressed to find a single website related to array formula's that doesn't inform the reader about how to enter them. What's the big difference between having to inform the user that to create an array formula you must type braces around it vs. telling the user that you must use Ctrl-Shift-Enter to create an Array formula? You have to tell the user something so that (s)he knows how to create an Array formula.

Fact is, Ctrl-Shift-Enter is actually 2 less key-presses. :-)

But hey, rest easy. Use Excel 365. I'm not sure how they have gotten around the text string issue, but I haven't spent much time (none, in fact) playing with array formulas in Office 365. Now, I've got to look up how to place {=SUM(A1:A5)} in an Office 365 cell without it turning into an Array formula. ;-) (Actually, I know how, but I'm going to keep it a secret.)

From: https://support.microsoft.com/en-us...

Beginning with the September 2018 update for Microsoft 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. This change in behavior is also accompanied by several new dynamic array functions. Dynamic array formulas, whether they’re using existing functions or the dynamic array functions, only need to be input into a single cell, then confirmed by pressing Enter. Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter. They’re commonly referred to as CSE formulas.


message edited by DerbyDad03


Reply ↓  Report •

#24
July 5, 2020 at 04:50:54
> There is nothing "arcane, hidden or essentially secret" about it.

Yes there is. Anyone looking at a listing of a computer program
in any language that I know anything about will see what all of the
keypresses were that created the listing. Someone looking at a
spreadsheet with curly braces around a formula would have no
clue that those curly braces can't be typed in, but instead have
to be inserted by pressing Ctrl-Shift-Enter. That keypress combo
is entirely hidden. Everything else is visible. The difference is
the difference between a program and the knowledge of how to
enter and run a program. In all other situations I know of, those
two categories of information are separate and distinct. In this
case, they are smushed together.

-- Jeff, in Minneapolis


Reply ↓  Report •

#25
July 5, 2020 at 07:08:04
And yet, no one that I know of has ever complained about the process, until now.

As I explained earlier, it resolves the "text entry" issue and it's a documented process.

And, as I said earlier, you can avoid the situation by using Office 365 - as long as you are willing to give up some minor features, such as anything related to VBA. Macros essentially do not exist in Office 365.

I'll take a known, documented and useful key press sequence any day as long as I can keep using VBA.

message edited by DerbyDad03


Reply ↓  Report •

#26
July 5, 2020 at 08:55:19
Jeff Root and DerbyDad03,

Interesting discussion on a topic that I know very little about. I'll use the workaround suggested by DerbyDad03 in #21.

Thanks to all for your assistance.
Brian W


Reply ↓  Report •

#27
July 5, 2020 at 13:39:32
Brian: You didn't answer my question.

Are you using Excel Online? If not, what version of Excel are you using?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#28
July 5, 2020 at 18:48:30
DerbyDad03,

I am using Excel 2019.

Brian W


Reply ↓  Report •

#29
July 5, 2020 at 20:20:27
If you are using Excel 2019 on a PC, then there is no reason that I can think of for the braces not to appear when you press Ctrl-Shift-Enter.

No, I take that back. I can think of one reason. I recall a post in another forum where the user discovered that only one Shift key worked for entering Array formulas. That is certainly not the norm. Perhaps he had a keyboard program installed that was intercepting the key sequence before Excel could process it.

If you'd be willing to try the following, i'd be interested in the result.

https://youtu.be/Nww5-MDdVn8

message edited by DerbyDad03


Reply ↓  Report •

#30
July 6, 2020 at 04:02:29
DerbyDad03,

Thank you for your suggestion. I do not have a "keyboard program installed that was intercepting the key sequence before Excel could process it." However, I did find out something VERY strange. I typically use the Shift key that is on the right side of the keyboard and when using that key with the <Ctrl> and <Enter> the braces did not appear in the array formula. When I used the Shift key that is on the left side of the keyboard with the <Ctrl> and <Enter> the braces DID appear and the formula worked correctly. Does this mean I have an issue with my laptop keyboard?

Thank you once again for your time and effort in resolving this issue,

Brian W


Reply ↓  Report •

#31
July 6, 2020 at 04:49:21
Thank you for that information. Since that is now the second time I've heard of that issue, I'll do some research.

Just from my own experience, Ctrl-Left Shift-Enter seems more natural to me. I'll let you know if I find any information on that issue, including testing my own system.

message edited by DerbyDad03


Reply ↓  Report •

#32
July 6, 2020 at 05:25:48
Brian:

Does your Excel recognize both shift keys when typing a text string, such as Text String?

(My system accepts either Ctrl and/or either Shift key for array formulas. Any combination works fine.)

message edited by DerbyDad03


Reply ↓  Report •

#33
July 6, 2020 at 08:24:57
FYI
On my Excel 2007
All combinations of CTRL (left / right)-SHIFT (left / right)-ENTER (keyboard / numpad)
work for creating an ARRAY formula.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#34
July 7, 2020 at 07:18:03
Brian:

Where are you located? Are you in the UK by any chance?

I heard from someone in the UK who said:

I wonder if this is perhaps a keyboard layout issue. I live in the UK and use a UK layout keyboard, which is different to the US layout. For me the right shift key has never worked to create array formulae and I have always had to use the left shift key.

message edited by DerbyDad03


Reply ↓  Report •

#35
July 7, 2020 at 07:41:24
DerbyDad03,

Excel recognizes both shift keys when entering a text string.

Thank you.
Brian W


Reply ↓  Report •

#36
July 7, 2020 at 07:42:52
DerbyDad03,

I am located in the United States.

Thank you.
Brian W


Reply ↓  Report •

#37
July 7, 2020 at 09:19:52
At this point, the only other suggestions I have are these:

1 - Ensure that your laptop is set up for a US keyboard, not a UK keyboard. It's not related to your physical location but how your laptop is configured.

2 - Try something like the Carnac Keys utility I mentioned earlier. My guess is that the system is not recognizing the Right Shift key when used in combination with the Ctrl key.

See here: https://youtu.be/Nww5-MDdVn8

message edited by DerbyDad03


Reply ↓  Report •

Ask Question