Zenlux Posted October 3, 2019 Share Posted October 3, 2019 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. Quote Link to comment
Boh Posted October 3, 2019 Share Posted October 3, 2019 You can have two database rows in the same worksheet. Have one for area x and one for area y. Quote Link to comment
Zenlux Posted October 3, 2019 Author Share Posted October 3, 2019 Oh really? Well well. I give it a go! Thanks! 1 Quote Link to comment
Zenlux Posted October 3, 2019 Author Share Posted October 3, 2019 (edited) 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 Quote Link to comment
Pat Stanford Posted October 3, 2019 Share Posted October 3, 2019 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. 1 Quote Link to comment
Pat Stanford Posted October 3, 2019 Share Posted October 3, 2019 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. 1 Quote Link to comment
Recommended Posts
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.