Trying to undertsand aggregate functions

SaladProblems

Active Member
Joined
Apr 9, 2010
Messages
279
Hi! I have a table of binders, and on each binder I have a box number.

There's supposed to be 14 binders in each box, and I'm trying to figure out how to display all boxes which do not contain 14 binders.

I can get a report to display a tally of boxes and do it indirectly, but I don't know of a good way to filter by the count of a field, I've been trying to build a query to do this and I've run into a wall with an aggregate function error message.

Is there a good resource to explain this, or is it something simple that I just don't understand?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Simply create a new query where you add your Box number field and one other field. Then click on the Aggregate Query button (looks like a Sigma). This will add a Totals Row with the words "Group By" under each field.

Just change the "Group By" expression under your "other" field to "Count". This will return a count for each field you Grouped By (Box number). Now just place <>14 in the Criteria field of your Count field.
 
Upvote 0
Worked perfectly, my problem was that I didn't think about what "group by" meant, and I kept grouping by and adding the wrong ones. Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top