Unique count with a sumifs

[EmmaTM]

Hi,
I have a table of staff data and I am currently doing this sumifs calculation to pull over hours worked (column R) using departments and cost centres (columns K, Q & Y) there may be multiple lines for 1 worker:
=SUMIFS(Data!$R:$R,Data!$K:$K,'370'!$C$1,Data!$Q:$Q,'370'!O9,Data!$Y:$Y,'370'!C9)
In another cell I want to count within this data how many workers there are - I have a unique personnel code in column L

Thank you
Emma

 

[6StringJazzer]

Do you have a header in row 1 of column L? What row does the data start in?

Something like

=UNIQUE(FILTER($L:$L, ($L:$L<>"")*($L:$L<>"Unique Personnel Code"))

 

[EmmaTM]

Hi Jeff,
Yes header in L is Personnel_Ref
Data will start in row 2

 

[EmmaTM]

Do I change this "Unique Personnel Code" to "Personnel_Ref"?

This is how my summary looks currently (I have changed my sumifs from when I first posted), I am looking for the unique count to go into column D where highlighted green.
The personnel code is on the data tab in column L, I think I need to incorporate the sumifs so that I get the correct count
Hope thats more helpful!

 

[6StringJazzer]

I don't understand what you are summarizing. What is the data in green?

Seeing the summary doesn't help much. I need to see the source data.

 

[EmmaTM]

Good morning,
This is my data sheet:

This is my summary:

I am using sumifs to pull in the hours worked (R) so item 1 on the summary for a Mod 3B4 (Y) working week days (Z) using the relevant PO number (K)
What I now need to do is count how many workers are included in that calculation by using column L Personell ref - this data needs to go into the green cells on my summary.

Please let me know if yu need me to clarify anything further?
Thank you

 

[6StringJazzer]

It is very difficult to work with just partial screenshots here. I can't see the cells referenced in your SUMIFS formula and you have not included row and column headers so I can't see what cell is what in your summary sheet.

The best I can do is make an educated guess based on your SUMIFS formula and what I see on your data sheet. Please try this for a count of employees included in the sum of hours:

=COUNTA(UNIQUE(FILTER(Data!$L:$L,(Data!$K:$K='370'!$C$1)*(Data!$Q:$Q='370'!O9)*(Data!$Y:$Y='370'!C9))))

 

[EmmaTM]

Test.xlsx
	E
37
4200023896

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Employer_Ref	Client_Ref	Client_Name	DocType	Tax_Year	Tax_Period	Invoice_Number	Invoice_Date	Timesheet_Number	Timesheet_Date	Purchase_Order	Personnel_Ref	First_Forename	Surname	Job_Title	NoVal	Description	Hours	Bill_Rate	Line_Bill_Amount	InfinisId	Info1	Info2	Info3	Info4	Info5	Employment_Type	Assignment_Ref
2				INV	2024	17	501512	01/08/2024	GEN000585384	26/07/2024	4200023900	M84065CA			Functional Tester		Travel	6	55	330	119949		Kilmarnock	63428	Mod 3B4	Week Day 06:00 Mon - 17:59 Fri	C	M72448	1,620.00	Timesheet 501512 - WK17		Included on PO4200023900 Worksheet
3				INV	2024	18	502059	08/08/2024	GEN000588216	02/08/2024	4200023900	M84065CA			Functional Tester		Travel	6	55	330	120955		Kilmarnock	63428	Mod 3B4	Week Day 06:00 Mon - 17:59 Fri	C	M72448	1,620.00	Timesheet 502059 - WK18		Included on PO4200023900 Worksheet
4				INV	2024	18	502059	08/08/2024	GEN000588216	02/08/2024	4200023900	M84065CA			Functional Tester		Hourly - Weekend Day or Night	11.5	65	747.5	120956		Kilmarnock	63428	Mod 3B4	Weekend 18:00 Fri - 05:59 Mon	C	M72448	1,620.00	Timesheet 502059 - WK18		Included on PO4200023900 Worksheet
5				INV	2024	18	502059	08/08/2024	GEN000588288	02/08/2024	4200023900	M84065CA			Functional Tester		Expenses	1	150	150	120957		Kilmarnock	63428	Mod 3B4	Expenses	C	M72448	1,620.00	Timesheet 502059 - WK18		Included on PO4200023900 Worksheet
6				INV	2024	22	503961	05/09/2024	GEN000597687	09/08/2024	4200023941	M27146CA			Tester In Charge		Hourly - Weekend Day or Night	11.5	90	1035	124523		Elderslie & Glenarnock	62683	TIC	Weekend 18:00 Fri - 05:59 Mon	C	M72648	1,020.00	Timesheet 503961 - WK22		Included on PO4200023941 Worksheet
7				INV	2024	23	504445	12/09/2024	GEN000599077	02/08/2024	4200023896	M27146CA			Tester In Charge		Hourly - Weekend Day or Night	11.5	90	1035	125492		Elderslie & Glenarnock	62683	TIC	Weekend 18:00 Fri - 05:59 Mon	C	M72452	1,020.00	Timesheet 504445 - WK23		Included on PO4200023896 Worksheet
Data

 

[EmmaTM]

Sorry I am really struggling to get my workbooks uploaded.

Test.xlsx
	P
23
4200023896

 

[6StringJazzer]

What happened when you tried my formula?