Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I'm sure this is an easy one, but....
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. |
#2
|
|||
|
|||
I'm sure this is an easy one, but....
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 "Omakbob" wrote: 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. |
#3
|
|||
|
|||
I'm sure this is an easy one, but....
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. "Omakbob" wrote in message ... 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. |
#4
|
|||
|
|||
I'm sure this is an easy one, but....
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? |
#5
|
|||
|
|||
I'm sure this is an easy one, but....
Well, that worked for one of the colums, but the others gave me a "0". I'll
keep trying.... thanks "Alan" wrote: 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. "Omakbob" wrote in message ... 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. |
#6
|
|||
|
|||
I'm sure this is an easy one, but....
Hi,
You may try the following array formula (Ctrl+Shift+Enter) =sum(if((range=specific category )*(range2=specific type),1,0)) Regards, "Omakbob" wrote: 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. |
#7
|
|||
|
|||
I'm sure this is an easy one, but....
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 "Omakbob" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OsCommerce - Easy Populate Script - CSV/TXT Conversion Problem. | Excel Discussion (Misc queries) | |||
Is there an easy way to create a calendar look up table in excel | Excel Discussion (Misc queries) | |||
new user with easy question? not easy for me | New Users to Excel | |||
Converting worksheets to workbooks. Is there an easy way? | Excel Discussion (Misc queries) | |||
Looking for an easy way to replace letters with acutes | Excel Worksheet Functions |