sumproduct - number of divers logging dives

[Leadbelly@46]

Building a Dive Record spreadsheet and Annual Statisics requirements to compute automatically. I have gotten this far:
=SUMPRODUCT(((M$10:$M$17="X")*($B$10:$B$17<>"")/COUNTIF($B$10:$B$17,$B$10:$B$17&"")))

Unfortuantely, I do not have a list of divers names for referencing. I am using these for testing purposes:
Column B Diver's Names: B. Dodson, L. Lloyd, D. Burch, C. Haley, J. Wilson, B.Dodson, B. Dodson
Columns M through BO (types of dives): will have "X's" in them if a specific type of dive was made, Scientific, Working, Training, Non-duty... Shore, Boat, Ice, Cave, etc..
I need to know how many Divers made the type of dives. For example, if I make three dives in Saltwater, L. Lloyd makes one, and D. Burch make one, the answer needs to show 3 which is correct. If Dodson makes one, Haley makes one, and Wilson makes one, the number is 2 (actually 2.33). It works in some cases and not in others
I am stumped!

Any assistance would be greatly appreciated. Leadbelly

[Tony Valko]

Looks like you're trying to get a unique count.

Do you want to count the unique entries in column B that correspond to "X" in column M?

[Leadbelly@46]

Unique that a good word for it I guess. Even if I made 100 of the dives I only want to be counted once. Does this help?
Leadbelly

[Tony Valko]

Even if I made 100 of the dives I only want to be counted once. Does this help?

Yes

So, does my question describe what you're wanting to do?

Do you want to count the unique entries in column B that correspond to "X" in column M?

[Leadbelly@46]

Yes sir, the entries in column B corresponding to "X" in M.

[Tony Valko]

Try one of these array formulas**...

If there won't be any empty cells within the range of column B:

=SUM(IF(FREQUENCY(IF(M2:M15="X",MATCH(B2:B15,B2:B15,0)),ROW(B2:B15)-ROW(B2)+1),1))

If there might be empty cells within the range of column B:

=SUM(IF(FREQUENCY(IF(M2:M15="X",IF(B2:B15<>"",MATCH(B2:B15,B2:B15,0))),ROW(B2:B15)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Leadbelly@46]

Thanks Tony, it appears to work. I do not anticipate blanks but just in case I used the second. I will play with it as I have not done anything with Frequency and understand exactly what it is doing. So I was way off base with the sumproduct idea - did not understand it any way!
Thanks again.
So if it works after playing with it how do I put "Solved" on the thread?
Brett

[Tony Valko]

You're welcome. Thanks for the feedback!

To mark a thread as being solved:

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.