COUNTIFS with Unique Values

Good Morning,

I am in need of some assistance in writing a formula to accomplish the goal of counting only unique text values in a column, based upon two different factors:


*Need to Count unique text values in column named 'CASE_NAME'
*Column 'FISCAL' must match cell A5
*Column 'ZONE' must match cell A3


If you can help, I would appreciate it very much.

Thank you,


Dennis G

Hard to tell without a worksheet what things should be, I've assumed fiscal is column D and zone is column C and that case_name is column B and entries are in rows 1 to 9.
=SUM(IF(C1:C9=A3,1,0)*IF(D1:D9=A5,1,0)*IF((COUNTIF(B1:B9,B1:B9)=1),1,0))
I've assumed that if a value is not unique, regardless of what is in fiscal and zone, it should be excluded.

Edit: taking into account Tony's post with named ranges:
=SUM(IF(ZONE=A3,1,0)*IF(FISCAL=A5,1,0)*IF((COUNTIF(CASE_NAME,CASE_NAME)=1),1,0))

Both are array formulas so need to be entered with ctrl+shift+enter

Tony, This works excellent, however I ran in to an issue. There are blank cells in the 'CASE_NAME' column, and in those instances the array formula is returning an #N/A error. Any way around that?