# Count cell by color with multiple conditions

#### samiesosa

##### New Member
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.

Excel Formula:
``=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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### samiesosa

##### New Member
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

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

Excel Formula:
``=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

##### New Member
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.

Replies
0
Views
55
Replies
1
Views
29
Replies
3
Views
144
Replies
6
Views
79
Replies
16
Views
340

1,148,339
Messages
5,746,177
Members
423,998
Latest member
eakenila

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back