Sum of values in one column based on values in a different column

[pierc52a]

I'm using Excel 2008 for Mac & have a very large data-set regarding Scuba which I will be constantly adding to. Also, once this gets all figured out, I intend on making a template to start fresh from each year.

Below is part of that data-set, lets say starting in A1: A=name, B= psi in, C=psi out, D=max depth, E=dive time, F= gear type

A B C D E F
Alyce 2800 700 34 37 Hookah
Brett 3000 700 35 35 Open Circ
Mike 3200 650 37 36 Open Circ
Suzie 2900 650 41 31 Rebreather
Laura 3000 700 42 40 Surf Supply
Mike 3000 800 45 31 Rebreather
Dean 2900 550 47 33 Surf Supply
Mike 2000 800 48 13 Open Circ
Mike 3000 800 48 31 Open Circ
Nathan 3100 600 48 34 Open Circ


Some names will appear multiple times, as above. I've been going round and round with functions and array functions and can't seem to get it right. Here is what I am trying to determine:

The number of divers (column A) using each GEAR TYPE (column F) category. For example: If looking for the number of divers who used Open Circ gear, I should end up with 3 Divers - Brett, Mike & Nathan

I have tried using a helper column but it is so cumbersome. If there is a way to do this simply with functions or array functions that would be best.

Thank you in advance for any help!!!

 

[Aladin Akyurek]

Try...

H2: Open Circ

=COUNTIF($F$2:$F$11,H2)

 

[pierc52a]

Thanks for the reply!

=COUNTIF($F$2:$F$11,H2) sums up the total "Open Circ" in column F, I am trying to determine how many unique values there are in column A that also, in the same row, say "open circ" in column F. Do you know how to do that? Perhaps my original post was un-clear...

as I said below, for the example data set I gave, the result should be 3.

Thank you!

 

[Aladin Akyurek]

Thanks for the reply!

=COUNTIF($F$2:$F$11,H2) sums up the total "Open Circ" in column F, I am trying to determine how many unique values there are in column A that also, in the same row, say "open circ" in column F. Do you know how to do that? Perhaps my original post was un-clear...

as I said below, for the example data set I gave, the result should be 3.

Thank you!

Right...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$11<>"",IF($F$2:$F$11=H2,
    MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0))),
     ROW($A$2:$A$11)-ROW($A$2)+1),1))

where H2 houses a condition like Open Circ.

 

[pierc52a]

SUCCESS! Thank you for you help

 

[Aladin Akyurek]

SUCCESS! Thank you for you help

Great. Thanks for the feedback.