COUNTIFS - But only count once for each like item

[RP1America]

Current formula:
=COUNTIFS($H:$H,"Procedure",$B:$B,">="&$M$3,$B:$B,"<="&$N$3)

Where:
H:H = General format
B:B = Date format
M3 = Low Date
N3 = High Date

Basically, I am currently using this to report how many instances of the word "Procedure" occurs in column H for a date range (M3 - N3).

However, I would like to remove the H:H, "Procedure" criteria and instead count by procedure form number stored in column F (general format).

Any given procedure form number (ex: RSPW-8HZP7F) could be listed multiple times. I would like to increase the count by 1 only if the form number has not been counted before. So although a specific form number may be listed 5 times, I only want to count it once.

Any thoughts on how to achieve this?

Thanks!!
Ryan

 

[SkipVought]

hi,

How about posting a sample table, representative of this issue, and the expected result.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Here is a sample:

http://www.mediafire.com/?mw64n8n31jllh9n

 

[SkipVought]

Some of us cannot access, due to company restrictions.

Please post in plain text.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and the expected result.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

AOCR-8HEPS6
CJDN-8GKNWY
CKCS-8HLPGC
CKCS-8HLPGC
CKCS-8HLPGC
CKCS-8HUKPL

Expected Result = 4

 

[SkipVought]

In general, for a SINGLE COLUMN of values...
[tt]
=SUM(IF(FREQUENCY(MATCH(Form,Form,0),MATCH(Form,Form,0))>0,1))
[/tt]
where Form is a named range.

If I had your requirement, I'd use the PivotTable wizard and then do my counts on the PivotTable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Unfortunately, I'm not understanding the formula. I am getting a #N/A.

Where Form is a named range, would they all be the same? As in:

=SUM(IF(FREQUENCY(MATCH(myRange1,myRange1,0),MATCH(myRange1,myRange1,0))>0,1))

 

[SkipVought]

Using your posted example, where you expect 4 unique values, and using your formula...
[tt]
=SUM(IF(FREQUENCY(MATCH(myRange1,myRange1,0),MATCH(myRange1,myRange1,0))>0,1))
[/tt]
the formula returns 4.

Is YOUR named range myRange1 in actuality?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Yes.


I selected the entire column F and gave it the name myRange1.

The column contains blank cells. Would this matter?

 

[SkipVought]

I selected the entire column F and gave it the name myRange1.

WHY??? if that were the case, why not just use F:F!!!

The Named Range is for the extent of a column of DATA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Sorry, from your original example, I gathered that that needed to be the case, "where Form is a named range".

I have now tried using F:F and am still receiving #N/A.

 

[RP1America]

Here is a small excerpt of column F.

Proc Form #
TDDN-8JYQ65
MJGG-8JXFQL
RSPW-8JVKRS



TDDN-8JNSNL
TDDN-8JCNVW
TDDN-8JCNX2
TDDN-8JCNXQ

SJPE-8JYPMB




SJPE-8J2RQG
SJPE-8JJKAB
SJPE-8J2RR5
SJPE-8J2RPE
SJPE-8JYPMB
SJPE-8JWMSK

 

[SkipVought]

Your EMPTY cells each need ONE SPACE.

Also, as an Excel 2007+ user, you ought to be using Structured Tables Insert > Tables > Table

Using YOUR heading in a Structured Table named Table1...
[tt]
=SUM(IF(FREQUENCY(MATCH(Table1[Proc Form '#],Table1[Proc Form '#],0),MATCH(Table1[Proc Form '#],Table1[Proc Form '#],0))>0,1))
[/tt]
returns 14 using your most recently posted sample data WITH a SPACE in each empty cell (otherwise #N/A!)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, one of the MANY advantages of using Structure Table References is that they automatically adjust as the number of rows in the table changes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

I'm using a shared workbook. It doesn't look like this is an available feature.

 

[SkipVought]

Then use your Named Range (NOT entire column!) Just the table rows.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Perhaps, I could copy the necessary data into a seperate workbook and create the table there via VBA?

I'm also wondering how I will be able to include the additional criteria (in this case a date range).

 

[RP1America]

So, if I select all current data and make it a named range, when a new row is added, will the named range expand accordingly?

 

[RP1America]

Just tried it and the named range did not expand. How would I go about doing this?