Can UNIQUE be used within COUNTIFS?

[Mariner]

I'm trying to count active cranes based on certain criteria. Normally I would use COUNTIFS to filter my key performance indicators to just hours and for the sum of activity over the trailing twelve months to be greater than 0. This would look something like this: +COUNTIFS($G$5:$G$885,$G925,$AP$5:$AP$885,">0").

The problem is if a given crane moved to another location within the period, it will show up again. That means I need to filter my formula to be sensitive to only unique values so I won't double count.

Below, you'll see that my initial count includes the highlighted crane twice.

How can I revise my COUNTIFS formula to only include unique values in column F? Or is there a better way altogether?

Thank you,

 

[Automatrix]

Do you have Office 365?

 

[jdellasala]

No, but COUNTIFS can be used with UNIQUE!

Book2
	A	B	C	D
1	Name	Count	Unique	Count
2	Felix	1	Aryanna	1
3	Rohan	1	Belen	1
4	Nyasia	1	Ezra	1
5	Tara	2	Felix	2
6	Aryanna	1	Glenn	1
7	Meagan	1	Jaheim	1
8	Jaheim	1	Meagan	1
9	Felix	1	Mekhi	1
10	Selina	1	Nyasia	1
11	Rohan	3	Rohan	3
12	Glenn	1	Selina	1
13	Ezra	1	Tara	1
14	Rohan
15	Belen
16	Mekhi
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=COUNTIFS(A2:A16,SORT(UNIQUE(A2:A16)))
C2:C13	C2	=SORT(UNIQUE(A2:A16))
D2:D13	D2	=COUNTIFS(A2:A16,C2#)
Dynamic array formulas.

Of course, with no context, it's kind of useless! Hence the SORT in the formula in B2, so that it would match the list in C2.
It could all go in one cell too:

Book2
	F
2	Aryanna - 1
3	Belen - 1
4	Ezra - 1
5	Felix - 2
6	Glenn - 1
7	Jaheim - 1
8	Meagan - 1
9	Mekhi - 1
10	Nyasia - 1
11	Rohan - 3
12	Selina - 1
13	Tara - 1
Sheet1

Cell Formulas
Range	Formula
F2:F13	F2	=SORT(UNIQUE(A2:A16)) & " - " & COUNTIFS(A2:A16,SORT(UNIQUE(A2:A16)))
Dynamic array formulas.

 

[JamesCanale]

In J10, it shows how many different cranes ran for at least one hour.

MrExcelPlayground14.xlsx
	F	G	H	I	J
8		Hours
9	Unit	KPI	Actuals
10	A	Hours	10		6
11	B	Hours	20
12	C	Hours	0
13	D	Hours	20
14	A	Hours	15
15	E	Hours	10
16	F	Hours	15
17	G	Hours
18	E	Hours	10
19	H	Hours	10
Sheet27

Cell Formulas
Range	Formula
J10	J10	=ROWS(UNIQUE(FILTER(F10:F19,(G10:G19=G8)*(H10:H19>0))))