Count numbers in text field

user_152935 0
Viewed 113

I have a spreadsheet that contains cells similar to this:

AAA Up 3
AAA Down 2
BBB Up 1
BBB Down 3
CCC Up 5
CCC Down 2

So it's always some sort of text, followed by Up or Down, and then how often it went up or down. I'd like to count how many times went up or down. In this example the total of Up would be 9, and Down 7. How do I do that in Excel/Google Sheets? I couldn't find a combination of functions that would help me get this problem solved.

I forgot to mention that each row here is one cell. Or in other words, all of the text above is one column only.

Thank you.

google-sheets-formula google-sheets
6 Answers

It's straightforward in Google Sheets:

=ArrayFormula(query(split(A:A," "),"select Col2,sum(Col3) where Col3 is not null group by Col2"))

enter image description here

user_38949170

Here is another suggestion.

A single formula, shorter than the one by Erik and more robust than the one by Tom.

=QUERY(INDEX(SPLIT(REGEXEXTRACT(A:A,"\w+ \d+$")," ")), 
         "select Col1, sum(Col2) where Col2 is not null group by Col1")

enter image description here

Functions used:

user_15277800

If we can assume only 1 or 2 digits in the number field then for Up use:

=SUMPRODUCT(--(RIGHT(A1:A6,2))*ISNUMBER(SEARCH(" Up ",A1:A6)))

and for Down use:

=SUMPRODUCT(--(RIGHT(A1:A6,2))*ISNUMBER(SEARCH(" Down ",A1:A6)))
user_24746560

There are many replies here. I'll suggest another and then tell you why it may be different:

=ArrayFormula(QUERY(SPLIT(REGEXEXTRACT(FILTER(A2:A,A2:A<>"")," ([UpDown]+ \d+)$")," ",0),"Select Col1, SUM(Col2) GROUP BY Col1 Label SUM(Col2) ''"))

It's not clear from your post whether "AAA," "BBB," etc. will be solid blocks of alpha-numeric information in your real application. I can imagine that the opening text might actually be strings with spaces (e.g., "Joe Schmoe from Idaho UP 3"). If so, some of the other formulas I see here will fail; but this formula will account for that.

user_139032830

Though a bit crude, but it'll work. Let us assume your data is in A2:A7 like this

AAAAAA Up 300
AAA Down 2
BBB Up 10
BBB Down 3
CCC Up 5
CCC Down 2

Enter this array formula in cell B2 (and copy downwards) [don't forget to use ctrl+shift+enter)

=VALUE(RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1))

Enter Up in C1 , Down in D1 and then enter this formula in C2 (and copy downwards and then rightwards so that cells C2:D7 are filled with this formula)

=--NOT(ISERROR(FIND(C$1,$A2)))

Now to sum Up values - use this formula

=SUMPRODUCT(C2:C7,B2:B7)

and for Down use this

=SUMPRODUCT(D2:D7,B2:B7)

You'll get desired sums

enter image description here

user_28848590

I see that a game of "whose is shorter" has begun. First (for those who don't know), a shorter formula doesn't necessary hold any advantages over a longer one as far as processing ability or speed. In fact, a formula that is a bit longer may hold benefits in terms of ease of understanding, getting rid of superfluous elements, etc.

That said, it seems to be, as I say, a sort of game for people who write formulas often to cut characters. So with only fun in mind, I toss this into the hat as well:

={"Up",SUM(FILTER(SPLIT(A2:A," "),FIND(" Up",A2:A)));"Down",SUM(FILTER(SPLIT(A2:A," "),FIND(" Down",A2:A)))}

It's shorter than the one by marikamitsos and gets rid of the "sum" header, which may or may not be wanted. It also allows for "Up" to be at the top without extra code (or by switching the clauses around, it gains no length and allows for "Down" to be at the top if desired).

user_139032830
Related