COUNTIFs with 2 or more criteria

[Notters]

Hi all, wondered if you could help. On the attached spread sheet in C4 of the values tab i need to count the number of unique values (no duplicates) in col F of the date drop tab based on the date in D4 looking at the dates in col H of the date drop tab.

I've probably not best expalined but the knida formula i'm after is

For C4 Count the number of unique referances (no duplicates) in col d data drop if the date in col H date drop is less than the date in D4

For C5 Count the number of unique referances in col d data drop if the date in col H data drop is equal to or greater than the date in D5 but less less than the date in E5

I take it i will then be able to copy down to get the rest?

[Marcol]

No attachment!

[DonkeyOte]

You have a few choices - you mention Col F as being source of unique values initially and then revert to Col D in your latter explanation - I've assumed you mean Col F.

Please Login or Register  to view this content.

In both of the above adjust ranges as necessary but given you're using an Array try to keep as lean as possible.

[DonkeyOte]

I don't see how the sample file (just) added relates to those ranges & sheet references outlined in your question.

I would suggest posting a sample that better reflects the actual requirements (per OP)

[Notters]

Hi all, wondered if you could help. On the attached spread sheet in C4 of the values tab i need to count the number of unique values (no duplicates) in col F of the date drop tab based on the date in D4 looking at the dates in col H of the date drop tab.

I've probably not best explained but the kinda formula I’m after is

For C4 Count the number of unique references (no duplicates) in col d data drop if the date in col H date drop is less than the date in D4

For C5 Count the number of unique references in col d data drop if the date in col H data drop is equal to or greater than the date in D5 but less than the date in E5

I take it i will then be able to copy down to get the rest?

[DonkeyOte]

Why have you posted this to a new thread rather than continuing with the pre-existing ?

You've since denoted the earlier thread as Solved (which it is not) and reposted the question albeit with better sample (as requested)...

http://www.excelforum.com/excel-gene...-criteria.html

[royUK]

Threads merged

[DonkeyOte]

@Roy, thanks

@Notters, per the PM you stated that the Arrays were not returning the correct results.

Can you perhaps outline what the results should be in your sample ?

Using the previously mentioned arrays (with ranges revised to account for sheetname & additional rows) the values returned would be:

C4: 37
C5: 118
C6: 80
C7: 129