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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,243
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,508
Messages
5,832,154
Members
430,113
Latest member
CoeurDeLion

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