Sumproduct just isn't enough

[johnturgoose]

I am trying to use the sumproduct function to count based on multiple criteria.

centre_code, dateR, trials_unit.

Basically I want to count the number of records where trials unit = X and DateR falls between 2 dates. Sumproduct... Easy. The problem is that there are multiple records with the same centre_code and what I really want to do is to count the number of different centre_codes that meet the criteria rather than the number of records.

Many thanks for your help

John

 

[ETID]

Two options...

Try using the pivot table wizard (it's intimidating at first, but very powerful)...and it leaves your original data unmodified

-or-

Look in the help on the DCOUNT() or DCOUNTA() functions

 

[macropod]

You could do this with a array formula (ie input with Ctrl-Shift-Enter instead of just Enter). The following formula is one example:

=SUM(IF((A1:A100="My Center")*(B1:B100>=DATE(2000,1,1))*(B1:B100<=DATE(2000,12,31)),C1:C100))

where:
'centre_code' is in A1:A100, and the 'centre_code' being tested is 'My Center'
'dateR' is in B1:B100, and the 'dateR'being tested is from 1/1/2000 to 31/12/2000 (or 12/31/2000 for the US)
'trials_unit' is in C1:C100

Instead of hard-coding the source ranges, you could use named ranges and, instead of hard-coding the criteria, you could use cell references.

Cheers