CODEWITHSUNDEEP
vbaexcel
Kevin
Kevin

Reputation: 567

How do I Sum a total based on Grouping

I've got data (which changes every time) in 2 columns - basically state and number. This is an example:

Example Data

State           Total
Connecticut     624
Georgia         818
Washington      10
Arkansas        60
New Jersey      118
Ohio            2,797
N. Carolina     336
Illinois        168
California      186
Utah            69
Texas           183
Minnesota       172
Kansas          945
Florida         113
Arizona         1,430
S. Dakota       293
Puerto Rico     184

Each state needs to be grouped. The groupings are as follows:

Groupings

**US Group 1**
California
District of Columbia
Florida
Hawaii
Illinois
Michigan
Nevada
New York
Pennsylvania
Texas

**US Group 3**
Iowa
Idaho
Kansas
Maine
Missouri
Montana
North Dakota
Nebraska
New Hampshire
South Dakota
Utah
Wyoming

Every other state belongs in US Group 2..

What I am trying to do is sum a total for each group. So in this example I would have totals of:

Totals

650 in Group 1 (4 states)
6365 in Group 2 (9 states)
1307 in Group 3 (3 states)

So what I would like to do each time I get a new spreadsheet with this data, is not have to create an if/countif/sumif formula each time. I figure it would be much more efficient to select my data and possibly run a macro which will do that (possibly checking against some legend or something)

Can anyone point me in the right direction? I have been banging my head against the VBA editor for 2 days now...

Upvotes: 1

Views: 184

Answers (2)

sous2817
sous2817

Reputation: 3960

Here is one way.

Step 1: Create a named range for each of your groups.

Step 2: Try this formula: =SUMPRODUCT(SUMIF(A2:A18,Group1,B2:B18))

Formula Breakdown:

A2:A18 is the the state names

Group1 is the named range that has each of your states in group 1

B2:B18 is the values you want to sum.

It's important that your state names and the values you want summed are the same size (number of rows). You should also standardize your state names. Having S. Dakota in your data and South Dakota in your named range won't work. Either add in the different variations of the state name(s) to your list, or standardize your data coming in.

To get a clear visual of what the formula is doing, use the Evaluate Formula button on the Formulas Tab, it will be much better than me trying to explain it.

EDIT

Try this formula for summing up values that are not in Group1 or Group3:

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(A2:A18,Group1,0)))),--(NOT(ISNUMBER(MATCH(A2:A18,Group3,0)))),B2:B18)

Seemed to work on my end. Basically it works by only summing valyes in B2:B18 where both match functions return N/A (meaning it's not in the defined group list).

Upvotes: 2

ergonaut
ergonaut

Reputation: 7057

Use a vlookup with a mapping of your states to groups. Then from the group number, add it if it's found, or add 0.

Upvotes: 0

Related Questions