Worksheet - Summarize items that match two fields?

[Zenlux]

Hi,

 

 

Does anyone know if it's possible to sum items that match two fields in worksheets?

 

I need to sum all items by type and by layer so I can get counts for chairs on area X and chairs on area Y on different rows of the same furniture report.

[Boh]

You can have two database rows in the same worksheet. Have one for area x and one for area y. 

[Zenlux]

Oh really? Well well. I give it a go! Thanks!

[Zenlux]

No. Failed. That wasn't what I was looking for.

 

I believe your solution was to do one list of objects for each area.

 

This is not what I was looking for. Let's try again with another type of an example.

 

I have different areas and their amount is changing all the time. The worksheet report should anyhow print out all the furniture in all active areas without me doing new records all the time. So - can I do one single worksheet report for furniture that summarizes items that match two different fields on record? Or a field and a layer? Or any combination?

 

I'm trying to automate the following:

 

Area1 Chair1 1pcs

Area1 Chair1 1pcs

Area2 Chair1 1pcs

Area2 Chair1 1pcs

Area2 Table1 1pcs

Area2 Table1 1pcs

 

into->

 

Area1 Chair1 2pcs

Area2 Chair1 2pcs

Area2 Table1 2pcs

Edited October 3, 2019 by Zenlux

[Pat Stanford]

Yes, you can.

 

Assuming you can create a criteria for the database rows that limits the selection to only items that are in two areas, add a column with a formula of:

 

=IF(((LOC='Area-1')), 'Area-1', 'Area-2')

 

This will give you the name of the area for all the items.  Click the disclosure triangle and choose the Summarize Items check box.

 

In the next column put a formula of:

 

=SYMBOLNAME

 

Click the disclosure triangle and choose the Summarize Items check box. This should give you a list showing one row for each symbol in each area.

 

In the next column put a formula of:

 

=Count

 

This will give you the number of each symbol that is summarized in each subrow.

 

 

[Pat Stanford]

The real trick to the above is to realize that any of the criteria can be used as a formula in a cell (either with or without the IF statement) to return a boolean (true or false) value that can then be used for other comparisons or summaries in the worksheet.