I'm sure this is an easy one, but....

[Guest]

I cannot figure it out for the life of me.
I have a table with 3 colums:
1) Category of plan (i.e. 1046, 1046-1)
2) Type of plan (med, dent, life)
3) Number of people in that plan.

What I need is a formula that will sum the total number of people in a
particular plan. Example: The sum of all people who are in a 1046 group and
are dental subscibers.

Any help would be appreciated.

 

[Guest]

I would suggest creating a pivot table report for this. Make Category and
Type row fields and make Number of People the value field.

Select a cell in the data range and then follow the menu path Data / Pivot
Table & Pivot Chart Report and follow the wizard's instructions. Excel help
is pretty good with this as well for a simple table like your issue.

Hope this helps.

Bill Horton

 

[Alan]

Witth the category in column A
The type in column B.
The number of people in column C
Try
=SUMPRODUCT(--(A1:A500=1046),--(B1:B500="Med"),--(C1:C100))
Adjust to suit, note that the search criteria can be in a cell, like instead
of (B1:B500="Med") you can use (B1:B500=G1) where G! contains Med,
Regards,
Alan.

 

[Guest]

Well, I guess there's 4 columns. The other column has the name of the
client. So, we have

Client Category Type (med,dental) # of subcribers


There are several clients , and we need the total number of subscibers for
each category and type.

Would a pivot table allow me to do this?

 

[Guest]

Well, that worked for one of the colums, but the others gave me a "0". I'll
keep trying....

thanks

 

[Guest]

Hi,

You may try the following array formula (Ctrl+Shift+Enter)

=sum(if((range=specific category )*(range2=specific type),1,0))

Regards,

 

[Guest]

Yes, pivot tables are very powerful and flexible. You may have to spend a
little time in the Excel help if you are new to them but the scenario you
list appears to be a very simple pivot table. Try it out. You can have
multiple fields for both page, row, and columns in pivot tables and can move
them around to get whatever views you want of the data. You can have
multiple data fields too. However, your scenario just has one data field (#
subscribers).

One suggestion would be to make Client and Category row fields, Type a
column field, and # of subscribers a data field. The pivot table wizard
walks you through the steps. You can then use the pivot table toolbar to
assist in making any adjustments that you may want.

Bill Horton