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?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,923
Office Version
  1. 365
Platform
  1. Windows
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.
 

SaladProblems

Active Member
Joined
Apr 9, 2010
Messages
279
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,645
Messages
5,597,343
Members
414,138
Latest member
Ankitjainkaka

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
Top