Countifs on Unique Records with Multiple Criteria

[03856me]

I have Excel 2007, using a Table called sawmillTable

I am looking for the count of days the machine ran. There are multiple days because of breaking down dimensions, specie, etc. So I need to ignore duplicates in the [date] field, I think.

If someone could help get me started that would be great. I have found formulas with MATCH, INDEX, INDIRECT - but have spent hours trying to modify for this purpose, with no luck. I have not been able to find any formulas using Tables and Field Names, which is giving me all kind of problems, along with the ignoring duplicate days.

Here is the criteria:
[Fiscal Year] = $D$3
[Month] = $C$3
[date] <=$B$3
[MachCtr] = "Sharp Chain"

Thank you in advance for your help.

 

[pliskers]

Have you tried a SUMPRODUCT? It would look something like this:

=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),--(Range3=Criteria3))

 

[T. Valko]

I have Excel 2007, using a Table called sawmillTable

I am looking for the count of days the machine ran. There are multiple days because of breaking down dimensions, specie, etc. So I need to ignore duplicates in the [date] field, I think.

If someone could help get me started that would be great. I have found formulas with MATCH, INDEX, INDIRECT - but have spent hours trying to modify for this purpose, with no luck. I have not been able to find any formulas using Tables and Field Names, which is giving me all kind of problems, along with the ignoring duplicate days.

Here is the criteria:
[Fiscal Year] = $D$3
[Month] = $C$3
[date] <=$B$3
[MachCtr] = "Sharp Chain"

Thank you in advance for your help.

Assumes no empty cells within the [date] range.

A3 = Sharp Chain

Array entered**:

=SUM(IF(FREQUENCY(IF(sawmillTable[MachCtr]=A3,IF(sawmillTable[Month]=C3,IF(sawmillTable[Fiscal Year]=D3,IF(sawmillTable[date]<=B3,MATCH(sawmillTable[date],sawmillTable[date],0))))),ROW(sawmillTable[date])-MIN(ROW(sawmillTable[date]))+1),1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[03856me]

thank you so much, the array formula worked perfectly.....greatly appreciated....

 

[T. Valko]

thank you so much, the array formula worked perfectly.....greatly appreciated....

You're welcome. Thanks for the feedback!