Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count based on multiple criteria fields w/duplicates
I have tried in vain to figure this out. It is similar to a previous post
that was not resolved. Can someone please help with a formula to find the number of each position listed in a table based on: session #, month, & state? The problem is that there are sometimes 2 different positions listed in one session (as shown below in sessions 1 & 6) and they both need to be counted. The rows are duplicated due to data in additional fields not shown in the example below. The results I am looking for would be in a new table with the position count formula at the intersection of columns listing the months and rows listing the states. So, the cell for January & IL would return a value of 3. February & CA would return a value of 2. All others would return a value of 1. Please note that while there are 4 sessions in January, there are a total of 5 positions to be counted for all states. Using Excel 2003. I would be very grateful for help with this problem. # Month State Position 1 January IL Manager1 1 January IL Manager1 1 January IL Manager2 1 January IL Manager2 2 January CA Supervisor1 2 January CA Supervisor1 3 January IL Manager1 3 January IL Manager1 4 January NY Director1 4 January NY Director1 5 February IL Director1 5 February IL Director1 6 February CA Director1 6 February CA Director1 6 February CA Director2 6 February CA Director2 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count based on multiple criteria fields w/duplicates
Hi,
So you want a table that looks like January February CA IL Where in this table are you handling session numbers? And in your example how did you come up with 3 for January IL? Even though I don't understand what you are doing, I would recommend a pivot table. You can quickly rearrange or filter it on any of the fields. For Example in the solution you want, it seems you need a separate range for each session? You can do that with one pivot table. Put the session in the Page field the state in the Row field, the Month in the column field and the position in the data field. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Excel-User-RR" wrote: I have tried in vain to figure this out. It is similar to a previous post that was not resolved. Can someone please help with a formula to find the number of each position listed in a table based on: session #, month, & state? The problem is that there are sometimes 2 different positions listed in one session (as shown below in sessions 1 & 6) and they both need to be counted. The rows are duplicated due to data in additional fields not shown in the example below. The results I am looking for would be in a new table with the position count formula at the intersection of columns listing the months and rows listing the states. So, the cell for January & IL would return a value of 3. February & CA would return a value of 2. All others would return a value of 1. Please note that while there are 4 sessions in January, there are a total of 5 positions to be counted for all states. Using Excel 2003. I would be very grateful for help with this problem. # Month State Position 1 January IL Manager1 1 January IL Manager1 1 January IL Manager2 1 January IL Manager2 2 January CA Supervisor1 2 January CA Supervisor1 3 January IL Manager1 3 January IL Manager1 4 January NY Director1 4 January NY Director1 5 February IL Director1 5 February IL Director1 6 February CA Director1 6 February CA Director1 6 February CA Director2 6 February CA Director2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count based on multiple criteria fields w/duplicates
Your requirement is not clear but you can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100=C2),-(D2:D100=D2)) This will give you the count of rows where Col A has the value A2, Col B has B2, Col C has C2 and Col D has D2. With your sample data it would give 2 You can adapt it to your situation... You can remove any set or add more columns... "Excel-User-RR" wrote: I have tried in vain to figure this out. It is similar to a previous post that was not resolved. Can someone please help with a formula to find the number of each position listed in a table based on: session #, month, & state? The problem is that there are sometimes 2 different positions listed in one session (as shown below in sessions 1 & 6) and they both need to be counted. The rows are duplicated due to data in additional fields not shown in the example below. The results I am looking for would be in a new table with the position count formula at the intersection of columns listing the months and rows listing the states. So, the cell for January & IL would return a value of 3. February & CA would return a value of 2. All others would return a value of 1. Please note that while there are 4 sessions in January, there are a total of 5 positions to be counted for all states. Using Excel 2003. I would be very grateful for help with this problem. # Month State Position 1 January IL Manager1 1 January IL Manager1 1 January IL Manager2 1 January IL Manager2 2 January CA Supervisor1 2 January CA Supervisor1 3 January IL Manager1 3 January IL Manager1 4 January NY Director1 4 January NY Director1 5 February IL Director1 5 February IL Director1 6 February CA Director1 6 February CA Director1 6 February CA Director2 6 February CA Director2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count based on multiple criteria fields w/duplicates
RR,
In E2, use the formula =SUMPRODUCT(($B$2:$B$17=B2)*($C$2:$C$17=C2)*($D$2: $D$17=D2)) and copy down toE3:E17 (change the 17s everywhere to match your data). Then use a formula like this =SUMPRODUCT(($B$2:$B$17="January")*($C$2:$C$17="IL ")/$E$2:$E$17) to extract your count of unique values for January and IL. Note that you could change "January" and "IL" in the formula to cell references (Cells containing January and IL), so that you could create a table by simple copying rather than by formula editing. HTH, Bernie MS Excel MVP "Excel-User-RR" wrote in message ... I have tried in vain to figure this out. It is similar to a previous post that was not resolved. Can someone please help with a formula to find the number of each position listed in a table based on: session #, month, & state? The problem is that there are sometimes 2 different positions listed in one session (as shown below in sessions 1 & 6) and they both need to be counted. The rows are duplicated due to data in additional fields not shown in the example below. The results I am looking for would be in a new table with the position count formula at the intersection of columns listing the months and rows listing the states. So, the cell for January & IL would return a value of 3. February & CA would return a value of 2. All others would return a value of 1. Please note that while there are 4 sessions in January, there are a total of 5 positions to be counted for all states. Using Excel 2003. I would be very grateful for help with this problem. # Month State Position 1 January IL Manager1 1 January IL Manager1 1 January IL Manager2 1 January IL Manager2 2 January CA Supervisor1 2 January CA Supervisor1 3 January IL Manager1 3 January IL Manager1 4 January NY Director1 4 January NY Director1 5 February IL Director1 5 February IL Director1 6 February CA Director1 6 February CA Director1 6 February CA Director2 6 February CA Director2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count based on multiple criteria fields w/duplicate
First of all, thanks to all who responded with answers to my problem. It took
a little manipulating but I was able to get it work for my needs. I used parts from both Sheeloo and Bernie's replies to obtain the proper results. In the helper column (E) I entered the formula =SUMPRODUCT(--($A$2:$A$251=A2),-($B$2:$B$251=B2),--($C$2:$C$251=C2),-($D$2:$D$251=D2)) and copied down. In the new table with the column headings for months & the row headings for the states, I entered the formula =SUMPRODUCT(($B$2:$B$251=H$5)*($C$2:$C$251=$G6)*($ D$2:$D$251<"")/$E$2:$E$251) where H5 is January and G6 is IL. This give me a total count of all positions in January for IL without counting duplicates, but does count the same position in different sessions. I think it's one of those things where you have to see everything to really "get it". Thanks again for your help! "Bernie Deitrick" wrote: RR, In E2, use the formula =SUMPRODUCT(($B$2:$B$17=B2)*($C$2:$C$17=C2)*($D$2: $D$17=D2)) and copy down toE3:E17 (change the 17s everywhere to match your data). Then use a formula like this =SUMPRODUCT(($B$2:$B$17="January")*($C$2:$C$17="IL ")/$E$2:$E$17) to extract your count of unique values for January and IL. Note that you could change "January" and "IL" in the formula to cell references (Cells containing January and IL), so that you could create a table by simple copying rather than by formula editing. HTH, Bernie MS Excel MVP "Excel-User-RR" wrote in message ... I have tried in vain to figure this out. It is similar to a previous post that was not resolved. Can someone please help with a formula to find the number of each position listed in a table based on: session #, month, & state? The problem is that there are sometimes 2 different positions listed in one session (as shown below in sessions 1 & 6) and they both need to be counted. The rows are duplicated due to data in additional fields not shown in the example below. The results I am looking for would be in a new table with the position count formula at the intersection of columns listing the months and rows listing the states. So, the cell for January & IL would return a value of 3. February & CA would return a value of 2. All others would return a value of 1. Please note that while there are 4 sessions in January, there are a total of 5 positions to be counted for all states. Using Excel 2003. I would be very grateful for help with this problem. # Month State Position 1 January IL Manager1 1 January IL Manager1 1 January IL Manager2 1 January IL Manager2 2 January CA Supervisor1 2 January CA Supervisor1 3 January IL Manager1 3 January IL Manager1 4 January NY Director1 4 January NY Director1 5 February IL Director1 5 February IL Director1 6 February CA Director1 6 February CA Director1 6 February CA Director2 6 February CA Director2 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to count based on multiple criteria fields w/duplicates
Hello,
I suggest to array-enter into a select range of 5 rows and 2 columns: =Pfreq(Pfreq(B24:B39&"-"&C24:C39,A24:A39,D24:D39)) [assuming your data resides in A24:D39] Pfreq you can find he http://www.sulprobil.com/html/pfreq.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count based on multiple criteria | Excel Worksheet Functions | |||
How do I get the count in excel based on criteria in multiple col | Excel Worksheet Functions | |||
count based on multiple date criteria | Excel Worksheet Functions | |||
how do i count values based on multiple criteria | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |