Formula to count based on multiple criteria fields w/duplicates

[Excel-User-RR]

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

 

[Shane Devenshire]

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.

 

[Sheeloo]

Your requirement is not clear but you can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100=C2),-(D2100=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...

 

[Bernie Deitrick]

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]

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!

 

[Bernd P]

Hello,

I suggest to array-enter into a select range of 5 rows and 2 columns:
=Pfreq(Pfreq(B24:B39&"-"&C24:C39,A24:A39,D2439))
[assuming your data resides in A2439]

Pfreq you can find here:
http://www.sulprobil.com/html/pfreq.html

Regards,
Bernd