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