Is there a function to find number of transactions?

user_4591175 0
Viewed 55

I have Google Sheets where I summarize my spendings based on categories.

For example "Online shopping" - =100+52+35+841

We can agree on there being 4 terms in this formula. That symbolize that I've made 4 transactions.

Ex.2 =18+45+124+1045-16

I got 16 back (maybe I made a part-return or got paid for something) This time it is 5 terms/transactions.

With the LEN + SUBSTITUTE function Google can "count" the amount of integers in that cell.

In ex. 2 that would return 13. (1,8,4,5...)

I want to count every term in the summery. So that the return is 5 for ex. 2 and 4 for example 1.

When that is possible it should be possible to count the amount of numbers with decimal representation. I.e. =25,45+109,05-34,5 should result in 3 transactions.

Also =12 should result in 1 transaction.

counting google-sheets symbolic-math if-statement regex
3 Answers

try:

=INDEX(IFERROR(1/(1/LEN(REGEXREPLACE(A1:A, "[0-9\.,=]", )))+1))

enter image description here

update 1:

=IFERROR(1/(1/LEN(REGEXREPLACE(FORMULATEXT(A15), "["" a-z0-9\.,=]", )))+1)

enter image description here

update 2:

=IF(A1="",,LEN(REGEXREPLACE(FORMULATEXT(A1), "["" 0-9\.,=]", ))+1)

enter image description here

user_56326290

If the only transactions are + or -, it's just the count of those symbols plus one.

Suppose your original strings were in A2:A. Place the following in B2 of an an otherwise empty B2:B (or in the Row-2 cell of any other open column):

=ArrayFormula(IF(A2:A="",,LEN(REGEXREPLACE(A2:A,"[^+-]",""))+1))

This one formula will process all entries in the range A2:A.

If you just want to process one cell instead of a contiguous range, you can use this formula, replacing A2 with your individual cell reference:

=LEN(REGEXREPLACE(A2,"[^+-]",""))+1

Either formula is just using REGEX to replace every character except + or - and then returning the LENgth of the remaining string plus one.

user_139032830

I found a custom solution based on @player0 answer, help and comments!

=IFERROR(1/(1/(1+LEN(REGEXREPLACE(FORMULATEXT(A1), "["" 0-9\.,=]", )))))

The 1+LEN() (instead of 1/(1/LEN())+1) is required to solve the problem where I only had one value. i.e. =25 to return 1 instead of nothing.

user_45911750
Related