How to Count how many numbers a sum contains?

English
#1

Hi Community,

I need some help. I am trying to create the following result. I have a cell that contains a sum function which sums a string of numbers that are written manually. At another cell I want to determine how many numbers are those.

For example Cell A1: =sum(18.9+22.9+34.9)
At the cell A2 I want to have the result 3

If at the A1 I would add another number =sum(18.9+22.9+34.9+12.9)
then the A2 should display 4

To be more specific, I have a salesman that makes sales during the hour. Every sale they make is put in the cell and sums with the previous so as for me to know the current turnover of the salesman. I also want to know how many orders the salesman has created.

Which formula should I use to the A2 cell to get this result?

Thank you a lot
Alejandro19061984

#2

=sum(18.9+22.9+34.9)

Why would you ever want to use “SUM” here? Who told you that? You only need to use

=18.9+22.9+34.9+12.9

And I’d suspect that the solution to the question you are asking would be just counting + characters in the cell’s formula.

1 Like
#3

And add 1 to the count result.
It may also be worth reviewing the SUBTOTAL() function when you have acquired a little more knowledge of LOCalc and start to use the autofilter.

#4

Hi there.

Indeed, this is how the sum is written. (I mean without the sum function but only with the = symbol).

How to I count the + ? Which is the coorect formula?

#5

Try

=LEN(FORMULA(A1))-LEN(SUBSTITUTE(FORMULA(A1);"+";))+1

#6

I would suppose the OriginalQuestioner and also other posters didn’t consider a few facts that may be relevant in the case but weren’t mentioned.

  1. Sums often do not only contain “numbers” but addends.
  2. Each addend may ba a number or a subexpression resulting in a number.
  3. Therefore the formulas =5 + 6 + 7 + 8 and =5 + (6 + 7) + 8 are assured to return the same result by the rules of arithmetic, and both of them contain 3 times the character “+”, but the first one has 4 addends while the second one has three.
  4. There are slightly generalized sums allowing also “-” in place of one or more “+”.
  5. Spreadsheets accept formulas like =18.9+34.9++++12.9 or =5+--+7.
  6. This is related to the fact that the symbols “+” and “-” are used (usable) in three ways:
    6.a. As a formal sign being part of the textual representation of the number. -3.1 or +2.7 e.g.
    6.b. As a right-looking operator changing the “value-sign” of a subexpression, a “-” then turning something negative to positive like in -(2-17) e.g.
    6.c. As the two-sided operator for addition or subtraction. (“Ordinary case”?)
  7. Thinking “I’m only interested in the ordinary case” may later lead into sitiuations where errors remain unnoticed or are hard to debug.
1 Like
#7

“Every sale they make is put in the cell…”
Well, you should not do that!

The sheet should be developed in a little way. This is correct when sales are recorded as separate records. It’s simple, and then the whole discussion becomes meaningless. Each value must be entered in a new record.

3 Likes
#8

Just to learn, I looked for an alternative to @sokol92 proposal:

=LEN(REGEX(A1;"[^\u002B:]";"";"g"))+1

where “A1” the cell with the sum, \u002B is for “+”, and the “^” is for no.

EDIT: There are some errors in my transcription. See @erAck comment.

I don´t know the spreadsheet layout, but for me it is easier to type the numbers one cell below the other (say in column D) and use =SUM(D:D) and =COUNT(D:D).

See more on Regular Expressions, and LibreOffice Help on REGEX and LEN.

#9

Just wanted to support @eeigor opinion. The best solution is to deal with trouble’s cause, not it’s consequences. If you need to analize data, you shall structure your data correctly, that will save tons of your time later on. @lupp showed you how many caveats there can (and definitely will) be. User input is the also very risky part, accidental data deletion while editing the same cell over and over again is pretty possible. Just refactor your spreadsheet, make data as atomic as possible/needed, use one row for one order (there are over 1M rows one one sheet, you’ll find this number overkill for your needs). This will allow you to analize data hourly, daily, weekly or whatever you like, find peak hours/days, create pivot tables and many more without overcomplicating things. Invest your time now and save much more time later.

2 Likes
#10

Instead of A1 it should be FORMULA(A1), the \u002B notation is unnecessary and can be written as + because within a character class set the otherwise metacharacters lose their meta property, and the : in the set is superfluous or even potentially harmful because it would split a cell range like A2:A4 as well.
The formula expression should be

=LEN(REGEX(FORMULA(A1);"[^+]";"";"g"))+1
#11

I read that you say the sales are identified manually; this sounds an expensive process. It also sounds as if you can identify each sale by salesman. Have you considered SUMIF and COUNTIF to extract the data?
This spreadsheet
SalesmanSum&Count.ods (12.7 KB)
has a list of sales people, their sales and a total sales per person and count per person. Is this the type of answer you are looking for? Cheers, Al

#13

You are right. Thanks for make me notice it!
I was transcribing from Spanish and forgot that.
But don’t remember why I finished with the \u002B instead of +. I will retest (Just done. It must have been the fear that the formula will not take the + symbol.)
EDIT: And the colon was an oversight. Sorry. I will use more zoom in this little screen.

#12

Considering the mentioned use-case I still cannot but completely dismiss the approach the question is based on. It simply makes no sense to discuss how to count addends in a formula if entering such formulas is obviously the wrong idea concerning the given task.
Suppose there is an open spreadsheet where somebody tracks ongoing business (sales) in a simple way. This is a case for entering data, not formulas. Formulas are made for evaluations. If any evaluation should be possibel beyond counting transactions (for which a tally-sheet is the better means), there must some data be put into a relation.
To better illustrate what I mean and want to tell the OQ, I made a little example. It demonstrates somehow the automatic generations of some related elements, the usage of the validity feature, and simple evaluation under a few aspects. See attachment.
disask66923SalespersonsSales.ods (300.6 KB)

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.