Modify COUNT

[Taproot]

Is there any way to modify the COUNT function in the worksheet?

I've set up a worksheet that acts as an electrical schedule. It pulls the Symbol Name from the symbol record to form the list. I have a symbol instance displayed beside the worksheet for graphic reference.

I would like to list the number of symbol instances in the drawing minus the one shown for the key. I've tried every permutation of =SUM(COUNT,-1) etc... to no avail. It appears that any formula combined with the COUNT function is calculated per instance (rather than once only).

I've also tried pasting different criteria - into the headers for the Item and Quantity columns, i.e. excluding the key layer from the Quantity criteria. However, in that case I get True/False returned (instead of numbers).

Any ideas would be appreciated.

[michaelk]

=COUNT(S='symbol name')-1

Or, (especially if the symbol has record(s) attached that you don't want on the schedule) you can "desymbolize" the symbol in the legend. Command-K or Modify>Convert>Convert to Group. Then you don't need to subtract 1.

hth

michaelk

[Pat Stanford]

MIchaels's idea of converting to a group may be the best, especially if you don't think the graphic of the symbol will change.

The other possibility is to put the legend symbols in a different class or layer and exclude that layer from the database criteria.

[Taproot]

Michael & Pat,

Thank you for the informed replies. I've been trying to fix the Image Uploader widget to give you a visual, but alas it seems to have broken. Here it is via a different service:

I think the Criteria route is the right one. De-symbolizing would eliminate the titles of the symbols.

I've set up the criteria of the Database header row to require Electrical Device Records to be present.

The Symbol column uses the formula:

=('Electrical Device'.'Type')

The first Qty column uses a simple COUNT formula.

The second Qty column uses the formula:

=COUNT((L<>'Keys & Legends'))

Wherein, I've tried to limit the criteria to any layer other than the layer the legend symbols are on.

As you can see, it returns "False" or a series of dashes rather than a number. Dashes are where I've put a test symbol (or a couple of symbols) to verify if the worksheet is working. I've tried setting the cell format to decimal, general, etc. to no effect. (At least no beneficial effect.)

It seems like what I'm trying to do should be possible ... but then again maybe not.

Thanks again for your help.

Edited June 2, 2010 by Taproot

[Guest Wes Gardner]

I think just placing the ones you don't want to count in a "NO" class seems to be the easiest...

The file "Counting Stuff on NO" is probably the one to use...

Edited June 2, 2010 by Wes Gardner

[Taproot]

Wes,

I didn't have any luck opening the attachments. Are these VW files? VW 2008 doesn't recognize the format. The icon suggests they are quicktime.

[Pat Stanford]

I don't know how you are creating this, but if I were starting from scratch I would probably do the following:

1. Create database row in a worksheet with a criteria of Record Electrical Device is present AND Layer IS NOT Keys and Legends.

2. SUMMARIZE the database based on the Electrical Device.Type column.

3. Put the worksheet on drawing on the Keys and Legends layer and put the sample symbols in the correct locations.

Is this close to what you have done?

[michaelk]

Not to confuse the issue...

But it might be easier to use a worksheet rather than a database so you can have more control over the order of the items in the key.

I find it easier and more flexible that way.

[Taproot]

Pat,

Yes, that's precisely what I've done. With the exception that I included the criteria (not key's and legends) in the QTY field only. I had wanted it to list all of the symbols (including the keys and legends layer), but only count the ones not on the keys and legends layer. Apparently, that is not possible.

It's just a pesky "1" - I have half a mind to just put a note at the bottom of the schedule instructing the electrician to deduct one from each line item! The perfectionist in me just can't quite settle for that.

Michaelk,

You may be right. Pardon my ignorance here, but is there a way to count symbols within a worksheet - not within a database row?

I'd be fine with listing out all of the symbols manually, but I don't know how to introduce COUNT functions into a worksheet. The only thing that I can think of is to make every row an individual database row where the title for each device is manually entered and the COUNT criteria for the whole row can be set to exclude the Key's & Schedules Layer. That seems like a LOT of work ... but I've tried a sample, and at least it functions correctly. Woah, sometimes the simplest stuff truly is the hardest.

[Pat Stanford]

More possible ways:

1. If you exclude the "Key" layer, then just a straight count should get you what you want.

2. If you don't exclude the "Key" layer then a formula of =count -1 in the header row should get you what you want, but it is then up to you to make sure you don't have a duplicate.

3. Many of the functions work both in worksheet cells and database columns. Something like:

=Count(('Electrical Device'.'Type' = 'switch'))

should get you the number of switches in the drawing in a single cell. The problem with doing it this way is you will have to manually edit the formula for every device type.

Be careful with the parenthesis. I think the extra set is required to work properly, but I have never gotten a good explanation of why. I recommend using the Paste Criteria command to generate the criteria. Click the Custom button to get more options.

[michaelk]

is there a way to count symbols within a worksheet - not within a database row?

Yes.

Attached is a trivial example of a way I often find useful. (I hope all this works in 2008)

The database can be used to quickly enter and change data connected to the symbols. The key is just a worksheet that counts specific symbols in a specific layer. It makes working with large amounts of data attached to symbols much easier to manage.

The symbols in the key are no longer symbols. They were inserted and converted to groups - so they won't get counted.

There are also symbols on another layer that are included in the database but not counted in the key. You can see how the key could easily be set up to count objects by layer or space or... ie you could easily count the number throw pillows by room, by floor, and by total project using this method.

I'll attach a 2010 file also. Pat's the master of this sort of thing, and I hope he (or any of the other users who know this stuff far better than I) will catch anything in the worksheets that isn't backwards compatible to 2008.

hth

michaelk

Edited June 9, 2010 by michaelk