 # Count Unique Records With Multiple Criteria With Formula

• Hi,
I'm looking for a formula approach for these please.

Count unique records in Column B where ...
1.)
... Column H >=A1 and <=A2

2.)
... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2

3.)
... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2

4.)
... Column H <>"" and Column I >=A1 and <=A2 and Column J =""

A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
Column B - 6 digit number (or blank)
Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)

Thank you,
Stefan

• Re: Count Unique Records With Multiple Criteria With Formula

It would be much more efficient to download and install the free add-in Morefunc.xll, and use the COUNTDIFF function. Otherwise, try the following formulas which need to be confirmed with CONTROL+SHIFT+ENTER...

Quote from StefanG

Count unique records in Column B where ...

1.)
... Column H >=A1 and <=A2

Code
``=COUNT(1/FREQUENCY(IF(H2:H100>=A1,IF(H2:H100<=A2,IF(B2:B100<>"",B2:B100))),IF(H2:H100>=A1,IF(H2:H100<=A2,IF(B2:B100<>"",B2:B100)))))``

Quote

2.)
... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2

Code
``=COUNT(1/FREQUENCY(IF(H2:H100<>"",IF(I2:I100>=A1,IF(I2:I100<=A2,IF(B2:B100<>"",B2:B100)))),IF(H2:H100<>"",IF(I2:I100>=A1,IF(I2:I100<=A2,IF(B2:B100<>"",B2:B100))))))``

Quote

3.)
... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2

Code
``=COUNT(1/FREQUENCY(IF(H2:H100+120>=A1,IF(H2:H100+120<=A2,IF(I2:I100="expired",IF(B2:B100<>"",B2:B100)))),IF(H2:H100+120>=A1,IF(H2:H100+120<=A2,IF(I2:I100="expired",IF(B2:B100<>"",B2:B100))))))``

Quote

4.)
... Column H <>"" and Column I >=A1 and <=A2 and Column J =""

Code
``=COUNT(1/FREQUENCY(IF(H2:H100<>"",IF(I2:I100>=A1,IF(I2:I100<=A2,IF(J2:J100="",IF(B2:B100<>"",B2:B100))))),IF(H2:H100<>"",IF(I2:I100>=A1,IF(I2:I100<=A2,IF(J2:J100="",IF(B2:B100<>"",B2:B100)))))))``

Hope this helps!

• Re: Count Unique Records With Multiple Criteria With Formula

I would use the database functions to do this sort of thing, in this case using a formula approach and DCOUNT (or DCOUNTA if pointing to other than numbers/dates).

See the attached.

## Files

[COLOR="Purple"]Regards,[/COLOR]
[COLOR="Purple"]Batman.[/COLOR]

• Re: Count Unique Records With Multiple Criteria With Formula

Batman,
Thank you for the sample. I will look this over. DCOUNT etc scares me even more then the other formula approach and I will try Domenics formulas first.
Thank you for your time and suggestion, i may be back.
Stefan

Edit: to provide answer to/for the right poster

• Re: Count Unique Records With Multiple Criteria With Formula

Quote from Domenic

Hope this helps!

Domenic,
Thank you so much - this is what I was looking for.
I can’t believe though, that I forgot to include an important factor. May I please trouble your expertise on this one more time please? Please accept my sincere apologies for this faux pa.

In addition to the given requirements, [COLOR="blue"]there is one more[/COLOR].
Count unique records in Column B where ...
1.)
... [COLOR="Blue"]Column C =A3[/COLOR] and Column H >=A1 and <=A2

2.)
... [COLOR="Blue"]Column C =A3[/COLOR] and Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2

3.)
... [COLOR="Blue"]Column C =A3[/COLOR] and Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2

4.)
... [COLOR="Blue"]Column C =A3[/COLOR] and Column H <>"" and Column I >=A1 and <=A2 and Column J =""

A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
[COLOR="blue"]A3 - customer code A (and later through A11 respectively B, C, D, E, F, G, H, I)[/COLOR]
Column B - 6 digit number (or blank)
[COLOR="blue"]Column C - customer code (A, B, C, D, E, F, G, H or I)[/COLOR]Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)

Thank you again,
Stefan

p.s.
I will also have a look into the add-in you suggested.

Okay, while i dont think that it will make any difference, i will mentioned that the references A1-A11 are on another sheet within the same workbook, the respective formulas will then be besides the customer code references.

___________

• Re: Count Unique Records With Multiple Criteria With Formula

Okay. I believe i figured out how the provided formulas work and how I can expand on these.
Unless i am mistaken, and the results which show to be correct, i can just add portions of IF's like "If(H2:H100>=A1" in the first part and the second part of the formula and add the respective amount of closing ")".

Out of interest, is there any limitation with these formulas?

Stefan

• Re: Count Unique Records With Multiple Criteria With Formula

Stefan,

By all means use Domenic's formula approach if this suits you better in this instance.

However, I would suggest that if you have requirements for analysis such as this that you familiarise yourself with the various database functions (DSUM, DCOUNT, DAVERAGE, etc) as these may well prove useful in slightly different circumstances. The principles are not that scary, althogh the formula approach that I have used takes a little more working out. Try the Excel Help, it should give you most of the answers.

[COLOR="Purple"]Regards,[/COLOR]
[COLOR="Purple"]Batman.[/COLOR]

• Re: Count Unique Records With Multiple Criteria With Formula

[QUOTE=StefanGUnless i am mistaken, and the results which show to be correct, i can just add portions of IF's like "If(H2:H100>=A1" in the first part and the second part of the formula and add the respective amount of closing ")".[/quote]

It looks like you've got it... Post back if you need further help.

[quote]Out of interest, is there any limitation with these formulas?[/QUOTE]

One can only nest up to 7 IF statments. However, the more conditions added, the less efficient it becomes.

Hope this helps!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!