worksheet sum formula with database rows

[805eng]

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. 

[Pat Stanford]

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.

 

 

[805eng]

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 

 

[Pat Stanford]

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.

[Tobias Kern]

Hi,

 

greetings.

 

This thread describes that we need new formulas like:
SumIF + countIF (with the ability to search inside database subrows).

 

Example:

=sumIF(H2='xx')

OR

=countIF(H2='xx)

 

It would be fantastic, if we could get new formulas like these.

 

Greetings from Germany

Tobi

[Pat Stanford]

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.

[Tobias Kern]

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

[Pat Stanford]

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.