Jump to content

worksheet sum formula with database rows


805eng

Recommended Posts

We'd like to sum our estimated cable length in a report. 

 

I seem to be able to sum "spreadsheet" values, but not "database" values

 

When I tried to sum a cell it only sees the whole integer 

example summing database rows H2.1 through H2.23 will show as  =sum(H2..H2) and report #Value!

 

of course if I sum spreadsheet rows H2 through h44 =sum(h2..h44) is works with no errors

 

There is a built-in sum values filter but, I don't see where the sum is posted. 

 

note: in the same column I do have record that report -- as their droppoints have not been placed on the plan design layer yet. 

Link to comment

You can't do anything manually with database subrows from a worksheet.

 

If you have numeric values in a database column, those values will automatically be SUMmed into the database header row. You can access that value elsewhere in the worksheet by just using the header row index so in your example =H2.

 

But that will only work if you want to sum everything in the column. Again from your example assuming the H2.1 through H2.23 are all of your database rows it will work. If you only want to SUM H2.11 through H2.19 this method will not work.

 

In a spreadsheet cell you can also specify a criteria as you do for a database so something like:

 

=SUM(((L='Design Layer-1') & (C='My Class')), your column H formula here) should work.

 

 

  • Like 1
Link to comment

Thx for the response. I see the "sum" value in the header and can add "=H2" in a spreadsheet row.

But it's not a sum so much as it is a count. 

 

There are 14 rows in this list (12 have estimated length)

6*12 = 72 

14 = rows/circuits in the report

 

We'd like to total the estimated cable length 

 

image.png.7e8e072fb7526b570ad803fdbf696baa.png

Link to comment

6ft is a string, not a number as far as a computer is concerned.

 

And what are the objects with dashes?  Are those objects that are SUMmarized and different summed object have different values?

 

Try using a formula of

 

=Value(TrimRight([your formula from H],'abcdefghijklmnopqrstuvwxys '))

 

This will remove any letters or blank spaces from the right end of the string and then convert the resulting string into a number.

 

Or post a sample file so I can actually see what you are working with.

  • Like 2
Link to comment
  • 2 weeks later...

In a Spreadsheet cell, you already have that ability. Most of the worksheet functions (Sum, Count, Area, etc) when used in a Spreadsheet cell allow you to use a Criteria as a parameter and then apply the function only to objects that meet the criteria.

 

So if the data in the database column H comes from a formula of MyRecord.MyField then you should be able to use something like:

 

=SUM(((Database criteria goes here) AND MyRecord.MyField='xx')))

 

I recommend using the criteria builder if you criteria for the database are at all complicated.

 

Ask again if you need more help.

  • Like 1
Link to comment

Hi Pat,

 

greetings and Thx for help.

 

I test your suggestion, and it works,

but I think it is too complicated / time-consuming for a “standard”-user.

 

Therefore, I think it would be useful to have extra formulas for

sumIF and countIF with the possibility to search inside database subrows,

with the same syntax, the Excel formulas work.

 

Greetings

Tobi

Link to comment

I like the wish, but I am not certain it will be granted until Worksheets get a ground up overhaul.

 

Because the Database Subrows are dynamic (change both when the objects in the drawing change, can be sorted differently, can be summarized differently), Doing anything with the data that comes from them is difficult.  What is cell 3.7 right now could be completely different the next time the worksheet is recalculated.

 

It might be possible to create a worksheet script that would do this, but I don't have the time to dig into it right now. 

 

If SumIF and CountIF are things you really think you want and would use, I recommend that you make a new thread in the Wishlist forum and request them.

  • Like 1
Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...