Count cell by color with multiple conditions

[samiesosa]

Hello,

Thank you in advance for anyone with advice. I've spent all day trying many different ways to tackle this and it's now 2am and I'm totally lost. My attempt at explaining myself below:

TEAM Sheet - This is a simplified version of how I plan which projects my team is working on, any given week. The numbers represent how many working days they have that week, and the colours represent the different projects they are working on. This sheet feeds into something bigger that i use for resource planning and is currently working for my needs so i can add to it, but cant change the data that's there too much (i.e. i can't change the numbers to letters)

4

MANAGER Sheet - Until now I've had one manager per department so haven't really had to manager the managers workload, but now i would like to see how many team members my managers are looking after in a given week per project, to see ahead of time if someone is overloaded.

I would like to change Project /Department in column B/C from a drop down, and then D<->S would populate number of coloured cells from those departments in each week.

I have a UDF that counts cells based on background colour. I can get it to sum an entire weeks column for one project. I can get it to sum an entire department for one project. But i can't figure out how to get it to consider both, and only give me the coloured cells if date in the header, and department match.

=SUMPRODUCT(--(ColorIndex(INDEX(TeamTable,MATCH(@departmentManagerTable,TeamTable[Department],0),MATCH(ManagerTable[[#Headers],[Mon 06 Sep 2021]],TeamTableHeader,0)))=ColorIndex([@Project])))

^ This formula gets me the right column, and the first row of the department, and will return a 1 if it's the right project colour, but I'm stuck on how to make it consider all the rows with the same department name.

should i be using offset formula to get the range instead? The spreadsheet i use is already very heavy, i use manual calculation and it takes about 5-10 seconds to finish calculating a sheet.

should i be trying to do this all in vba code and try expand on the count by cell colour adding conditions?

Totally understand if no one has time to fully solve this for me, very interested in any general ideas about how you'd approach it.

Thank you Thank you

 

[samiesosa]

I copied my spreadsheet incorrectly, unsure how to edit the post above, so trying again.

TEAM SHEET:

Department	Department2	Mon 30 Aug 2021	Mon 06 Sep 2021	Mon 13 Sep 2021	Mon 20 Sep 2021	Mon 27 Sep 2021	Mon 04 Oct 2021	Mon 11 Oct 2021	Mon 18 Oct 2021	Mon 25 Oct 2021	Mon 01 Nov 2021	Mon 08 Nov 2021	Mon 15 Nov 2021	Mon 22 Nov 2021	Mon 29 Nov 2021	Mon 06 Dec 2021	Mon 13 Dec 2021
Apple Team Member 01	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 02	APPLE					1	2.5	2.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2.5
Apple Team Member 03	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 04	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 05	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 06	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 07	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 08	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 09	APPLE	1.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2.5
Apple Team Member 10	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 11	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 12	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 13	APPLE	1.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2.5
Apple Team Member 14	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 15	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 16	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 17	APPLE	5	5	5	1	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 18	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Apple Team Member 19	APPLE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 01	ORANGE	2.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2.5
Orange Team Member 02	ORANGE	2.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	2.5
Orange Team Member 03	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 04	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 05	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 06	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 07	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 08	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 09	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 10	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 11	ORANGE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Orange Team Member 12	ORANGE										4	5	5	5	5	5	5
Orange Team Member 13	ORANGE										4	5	5	5	5	5	5
Orange Team Member 14	ORANGE										4	5	5	5	5	5	5
Orange Team Member 15	ORANGE										4	5	5	5	5	5	5
Cake Team Member 01	CAKE	2.5	2.5	2.5	2	2.5	2.5	2.5	2.5	2.5	1.5	2.5	2.5	2.5	2.5	2.5	2.5
Cake Team Member 02	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 03	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 04	CAKE	3	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 05	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 06	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 07	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 08	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 09	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 10	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 11	CAKE				4	5	5	5	5	5	4	5	5	5	5	5	5
Cake Team Member 12	CAKE	5	5	5	4	5	5	5	5	5	4	5	5	5	5	5

MANAGER SHEET

Manager	Project	Department	Mon 30 Aug 2021	Mon 06 Sep 2021	Mon 13 Sep 2021	Mon 20 Sep 2021	Mon 27 Sep 2021	Mon 04 Oct 2021	Mon 11 Oct 2021	Mon 18 Oct 2021	Mon 25 Oct 2021	Mon 01 Nov 2021	Mon 08 Nov 2021	Mon 15 Nov 2021	Mon 22 Nov 2021	Mon 29 Nov 2021	Mon 06 Dec 2021	Mon 13 Dec 2021
manager 1	Project 1	APPLE	19
	Project 2	APPLE	8
	Project 3	APPLE	4
	Project 4	APPLE	8
	Project 5	APPLE	2
TOTAL			30
manager 2	Project 1	ORANGE
	Project 2	ORANGE
TOTAL
manager 3	Project 1	CAKE
	Project 2	CAKE
TOTAL
manager 4	Project 3	ORANGE
	Project 3	CAKE
TOTAL
Manager 5	Project 4	ORANGE
	Project 4	CAKE
TOTAL
Manager 6	Project 5	ORANGE
	Project 5	CAKE
TOTAL

 

[samiesosa]

No idea if this simplifies everything i've said above or makes things more complicated

=SUMPRODUCT((Cell_Color(TeamTable[Header)=Cell_Color(ProjectColour))*(TeamTable[Department]=DepartmentName))

So ideally id like something like this, but can you use an indirect formula so the "header" call to the team table isn't set and can be based on a matching header for the manager sheet?

 

[samiesosa]

Hi, I've managed to make this work for myself, by have two spreadsheets instead of trying to do it all in one, and now the calculation of the data table isn't too heavy.