worksheet sum

[fuberator]

Hi

 

What is the syntax for summing a bunch of cells? Say I have numbers in B3, H4 and J8 for example. I can't do =SUM(B3+H4+J8), so what is the correct way?

 

Thank you 

[Pat Stanford]

If you just want a few cells you don't need the sum you can just add them up:   =B3+H4+J8

 

If you have a larger range you can specify the range by using two periods between the beginning and end of the range plus a Sum:    =SUM(A3..D3)

 

If you have a discontinuous (or continuous) range you can also use a comma as a separator in a Sum:    =SUM(B3, H4, J8)

 

Or you can combine the two styles:    =SUM(A3..D3, H8)

 

HTH

[fuberator]

thank you!

[MartinBlomberg]

I can't really understand why they don't stick to the Excel standard, I need to google this all the time.

[fuberator]

On 5/4/2022 at 4:00 PM, MartinBlomberg said:

I can't really understand why they don't stick to the Excel standard, I need to google this all the time.

 

could not agree more!

[Pat Stanford]

Do you realize that the VW worksheet is actually OLDER than Excel? So why didn't Excel adopt the VW standard (which I think was actually the Visicalc standard)?

[fuberator]

On 5/8/2022 at 9:39 PM, Pat Stanford said:

Do you realize that the VW worksheet is actually OLDER than Excel? So why didn't Excel adopt the VW standard (which I think was actually the Visicalc standard)?

 

 

Cosmic coincidence I was just watching this

 

  

  

[unearthed]

On 5/5/2022 at 2:00 AM, MartinBlomberg said:

I can't really understand why they don't stick to the Excel standard, I need to google this all the time.

Also VWs worksheet is so undocumented, sometimes things that should work just don't. I've got a long text file of solutions from working with it since ~2006.

[MartinBlomberg]

On 5/8/2022 at 9:39 PM, Pat Stanford said:

Do you realize that the VW worksheet is actually OLDER than Excel? So why didn't Excel adopt the VW standard (which I think was actually the Visicalc standard)?

Had no clue, nice to know 😃

So, how do I make these numbers add upp? All I get is "12". The line names are "2.1, 2.2, 2.3" and so on.... 

Thanks!

Edited March 20, 2023 by MartinBlomberg

[Tom W.]

56 minutes ago, MartinBlomberg said:

Had no clue, nice to know 😃

So, how do I make these numbers add upp? All I get is "12". The line names are "2.1, 2.2, 2.3" and so on.... 

Thanks!

 

I think the fact you have a 12 in the column header indicates the values are text rather than numbers or integers. If the data type was number or integer the values would be summed in the header + it would just be a matter of entering =D2 in a cell to show the total

[MartinBlomberg]

46 minutes ago, Tom W. said:

 

I think the fact you have a 12 in the column header indicates the values are text rather than numbers or integers. If the data type was number or integer the values would be summed in the header + it would just be a matter of entering =D2 in a cell to show the total

Thanks! So instead of using the SUM-function, I'll just use "=D2". Did I get that right?

[Tom W.]

7 minutes ago, MartinBlomberg said:

Thanks! So instead of using the SUM-function, I'll just use "=D2". Did I get that right?

 

It's not the formula that's the problem is the data. Where is it coming from? These are Amps from a record format right? I think you need to edit the record + change the data type for that field from text to integer. Then it will show the summed value in D2. I think.

[Pat Stanford]

Another thing to try is to use the Worksheet Format menu and format cell D2 (which will then flow down to all the subrows) as a number instead of as text. That MIGHT solve the problem.

 

VW (and basically every other program) has the ability to store data as either numbers or as a string of characters. If the values are a string of characters, say 1 6 5 3, then out brains automatically "see" a number 1653. But the program does not know that unless you specifically tell it to do so.

 

If you know the data in that field will never contain anything except digits, you could also use a formula of =Value(whatever formula you already had in D2). The Value function converts a character string that represents a number into the numeric value.  But if someone enters the value as 16 Amps instead of just 16, the value function will break.

 

So back to @Tom W.'s statement, it is not a formula problem, it is a data problem.

 

And once you get the correct values into the column, then yes, all you need to do to access the automatically summed value somewhere else in the worksheet is is a formula of =D2.

 

HTH

[MartinBlomberg]

On 3/20/2023 at 4:34 PM, Tom W. said:

 

It's not the formula that's the problem is the data. Where is it coming from? These are Amps from a record format right? I think you need to edit the record + change the data type for that field from text to integer. Then it will show the summed value in D2. I think.

Thanks! You're right @Tom W., the information I use comes from a record. I use Pop-up as function. Isn't it possible to use popup with a sum-function?

 

@Pat Stanford, the fields will never contain anything but digits, so it shouldn't be a problem then right?

I did some updates on my drawing and worksheet, here's how it looks now:

Here' a screenshot of my record format it might supply with some info. 

 

 

(I've used "Name" to connect with a data visualization, just so you know 😃

 

Thanks all! // Martin

[Tom W.]

4 minutes ago, MartinBlomberg said:

I use Pop-up as function. Isn't it possible to use popup with a sum-function?

 

Good question! Doesn't look like it but @Pat Stanford or someone else will confirm...

[Pat Stanford]

I believe that the data stored in popups is always defined as Text rather than numbers.

 

Try setting the formula in D3 to 

 

=Value('Power Record'.'Amp')

 

HTH

[MartinBlomberg]

On 3/22/2023 at 3:53 PM, Pat Stanford said:

I believe that the data stored in popups is always defined as Text rather than numbers.

 

Try setting the formula in D3 to 

 

=Value('Power Record'.'Amp')

 

HTH

That worked like a charm!! Wow, thank you! I would never had figured that out!

 

Many thanks!

[Pat Stanford]

The only trick to the above is that what you pass to the Value function must be characters that make up a number only. No unit marks, no suffix, no prefix, no extra spaces.

 

No Thousands separator.

 

Decimal point is OK.

 

Good

123

123.456

 

Bad

1,234

1,234.56

[MartinBlomberg]

Many thanks! I keep on going then 😃 New issue that I ran in to today.

How can I sum up the different values in this instance? 

 

 

Every bench can hold 4 persons, the chairs can hold 1 person. I want to sum up the totals to make it easy for client to understand how many benches and chairs that are needed, and how much people will fit on each seating type. So when I do =B2*C2 I get the total amount (284*6) instead of 234*4. How can I do this for each row to make the Total sum correlate with each type of seating? I want a sum for 2.1, 2.2 and 2.3 separated.

 

Thank you for all your help!

 

martin 

[Pat Stanford]

Did you ever get this working for your needs? If not can you post a sample file so I can take a look using your configuration.

[MartinBlomberg]

On 4/7/2023 at 5:18 PM, Pat Stanford said:

Did you ever get this working for your needs? If not can you post a sample file so I can take a look using your configuration.

Sorry, I just found out that I was logged out from this forum somehow and didn't get any notifications that I got a reply from you.

No, still in need of a solution for the thing above. Actually what I want is something like this:

Column A          Column B          Column C          Column D          

Setting type.      # of units.         # of ppl/unit.     Total of people

 

In Excel I just use the sum function in column D, but in VW I can't... Thanks!

[Pat Stanford]

You don't need a function, just a math operation. If your database header is in Row 3, then set the formula for Column D (cell D3) to:

 

=B3*C3

 

The same would work in Excel.

 

You are the second person who has come from Excel with the misunderstanding that you need the SUM function to do what is actually just a simple multiplication. Do you know why you learned to use SUM in excel? It makes sense for ranges of cells, but to just multiply one cell times another it seems overkill.

 

 

[MartinBlomberg]

On 4/21/2023 at 5:47 PM, Pat Stanford said:

You don't need a function, just a math operation. If your database header is in Row 3, then set the formula for Column D (cell D3) to:

 

=B3*C3

 

The same would work in Excel.

 

You are the second person who has come from Excel with the misunderstanding that you need the SUM function to do what is actually just a simple multiplication. Do you know why you learned to use SUM in excel? It makes sense for ranges of cells, but to just multiply one cell times another it seems overkill.

 

 

Thank you @Pat Stanford! Maybe I'm really stupid, but I can't get that to work. When I try to type in Column D, I can't. Not sure why. But, if I move down to Row 4 (or 5 and so on), it works. I want 228*4 in this case (Column B*Column C). 

 

Thank you again!

[Tom W.]

Cell D3 like Pat said. The database header row (with the small diamond) not one of the database rows (.1, .2, etc)

[MartinBlomberg]

9 minutes ago, Tom W. said:

Cell D3 like Pat said. The database header row (with the small diamond) not one of the database rows (.1, .2, etc)

@Tom W. and @Pat Stanford aaaaaaah. I knew I missed something. You're the best. Thank you!!