Count Unique Values with Multiple Conditions

[stbrooks13]

I need to count the number of unique offices by summary position=Associate, fiscal year=2014, offer acceptance date>0 and school=I1 (which is a drop down box for all the schools we recruit from). Please help! I've been scouring the web for hours. My ranges are named, I am able to come up with a value for one condition (summary position), but I don't know how to add more IF conditions:

=SUM(IF(FREQUENCY(IF(SummaryPosition="Associate",MATCH(Office,Office,0)),ROW(Office)-ROW($E$2)+1),1))

 

[steve the fish]

Is it something like:

=COUNTIFS(SummaryPosition,"Associate",FiscalYear,"2014",OfferAcceptanceDate,">0",School,$I$1)

 

[stbrooks13]

Is it something like:

=COUNTIFS(SummaryPosition,"Associate",FiscalYear,"2014",OfferAcceptanceDate,">0",School,$I$1)

Hi Steve the Fish! That will count the number of schools, not the unique number of schools, won't it?

 

[GerryZ]

@stbrooks13
Hi can you put a Sample, like a screenshot so we can void to put 1000s o words.
that would much easier thank you very much!

 

[steve the fish]

Sorry misread. Im presuming the offices are text.

=SUM(IF(FREQUENCY(IF(SummaryPosition="Associate",IF(FiscalYear=2014,IF(OfferAcceptanceDate>0,IF(School=I1,MATCH(Office,Office,0))))),IF(SummaryPosition="Associate",IF(FiscalYear=2014,IF(OfferAcceptanceDate>0,IF(School=I1,MATCH(Office,Office,0))))))>0,1))

You must enter with CNTL-SHIFT-ENTER

 

[GerryZ]

Hi all
try this!

=SUM(IF(FREQUENCY(IF(SummaryPosition="Associate",IF(FiscalYear=2014,IF(OfferAcceptanceDate>0,IF(School=I1,MATCH(Office,Office,0))))),ROW(office)-row(A1)+1);1)

GerryZ

 

[steve the fish]

Hey Gerry. You forgot your true portion but apart from that it works

 

[GerryZ]

@Hi Steve
unfortunatly I didn't try with Excel otherwise i would correct the misteks, I copied your formula down and i fast reply I was sure the were some errors like Parentesis, coma, ecc..

What I did I correct the FREQUENCY argument ITEM CLASS that need {1.2.3.4.5.6.7.....}for the match
I hope i helped!!

 

[GerryZ]

@Steve the fish
I don't have Excel can you post the correct formula for for STbrooks13
and if you can small screen -shoot?
thank you steve the fish

 

[steve the fish]

My other formula would work but yours would be:

=SUM(IF(FREQUENCY(IF(SummaryPosition="Associate",IF(FiscalYear=2014,IF(OfferAcceptanceDate>0,IF(School=I1,MATCH(Office,Office,0))))),ROW(Office)-ROW(A1)+1)>0,1))

Again CNTL-SHIFT-ENTER