Step One: All data must be in one table. If the ten worksheets cannot be
consolidated into one worksheet then the only option is to consolidate it
into one table in Access. For this example the worksheet should be named
€œAll€�.

Step Two: Create a new worksheet with columns State, County, Tons and
Commodity. Row 1 should have these names.

Step Three: Key in each possible combination of State, County and Commodity
in the rows of the new sheet (Assuming Columns A, B and D). Something like:

State County Tons Commodity
IA ADAMS AMMONIUM NITRATE
NE ADAMS AMMONIUM NITRATE
OK ALFALFA AMMONIUM NITRATE
IA ALLAMAKEE AMMONIUM NITRATE
KS ALLEN AMMONIUM NITRATE
LA ALLEN AMMONIUM NITRATE


Step Four: Enter this formula in column C (Tons) Row 2, and then copy it
down to the end of the used rows.


=SUMPRODUCT(--(All!$A$2:$A$65000=A2),
--(All!$B$2:$B$65000=B2),
--(All!$D$2:$D$65000=D2),All!$C$2:$C$65000)

Cant get it on one line, but it is one formula.

That should do it. You can then filter to exclude zero tons if that helps.

Tom