Count cell by color with multiple conditions

S

samiesosa

New Member
Joined
Jan 14, 2013
Messages
4
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
I copied my spreadsheet incorrectly, unsure how to edit the post above, so trying again.

TEAM SHEET:
DepartmentDepartment2Mon 30 Aug 2021Mon 06 Sep 2021Mon 13 Sep 2021Mon 20 Sep 2021Mon 27 Sep 2021Mon 04 Oct 2021Mon 11 Oct 2021Mon 18 Oct 2021Mon 25 Oct 2021Mon 01 Nov 2021Mon 08 Nov 2021Mon 15 Nov 2021Mon 22 Nov 2021Mon 29 Nov 2021Mon 06 Dec 2021Mon 13 Dec 2021
Apple Team Member 01APPLE5554555554555555
Apple Team Member 02APPLE12.52.52.52.522.52.52.52.52.52.5
Apple Team Member 03APPLE5554555554555555
Apple Team Member 04APPLE5554555554555555
Apple Team Member 05APPLE5554555554555555
Apple Team Member 06APPLE5554555554555555
Apple Team Member 07APPLE5554555554555555
Apple Team Member 08APPLE5554555554555555
Apple Team Member 09APPLE1.52.52.522.52.52.52.52.522.52.52.52.52.52.5
Apple Team Member 10APPLE5554555554555555
Apple Team Member 11APPLE5554555554555555
Apple Team Member 12APPLE5554555554555555
Apple Team Member 13APPLE1.52.52.522.52.52.52.52.522.52.52.52.52.52.5
Apple Team Member 14APPLE5554555554555555
Apple Team Member 15APPLE5554555554555555
Apple Team Member 16APPLE5554555554555555
Apple Team Member 17APPLE5551555554555555
Apple Team Member 18APPLE5554555554555555
Apple Team Member 19APPLE5554555554555555
Orange Team Member 01ORANGE2.52.52.522.52.52.52.52.522.52.52.52.52.52.5
Orange Team Member 02ORANGE2.52.52.522.52.52.52.52.522.52.52.52.52.52.5
Orange Team Member 03ORANGE5554555554555555
Orange Team Member 04ORANGE5554555554555555
Orange Team Member 05ORANGE5554555554555555
Orange Team Member 06ORANGE5554555554555555
Orange Team Member 07ORANGE5554555554555555
Orange Team Member 08ORANGE5554555554555555
Orange Team Member 09ORANGE5554555554555555
Orange Team Member 10ORANGE5554555554555555
Orange Team Member 11ORANGE5554555554555555
Orange Team Member 12ORANGE4555555
Orange Team Member 13ORANGE4555555
Orange Team Member 14ORANGE4555555
Orange Team Member 15ORANGE4555555
Cake Team Member 01CAKE2.52.52.522.52.52.52.52.51.52.52.52.52.52.52.5
Cake Team Member 02CAKE5554555554555555
Cake Team Member 03CAKE5554555554555555
Cake Team Member 04CAKE3554555554555555
Cake Team Member 05CAKE5554555554555555
Cake Team Member 06CAKE5554555554555555
Cake Team Member 07CAKE5554555554555555
Cake Team Member 08CAKE5554555554555555
Cake Team Member 09CAKE5554555554555555
Cake Team Member 10CAKE5554555554555555
Cake Team Member 11CAKE4555554555555
Cake Team Member 12CAKE555455555455555


MANAGER SHEET
ManagerProjectDepartmentMon 30 Aug 2021Mon 06 Sep 2021Mon 13 Sep 2021Mon 20 Sep 2021Mon 27 Sep 2021Mon 04 Oct 2021Mon 11 Oct 2021Mon 18 Oct 2021Mon 25 Oct 2021Mon 01 Nov 2021Mon 08 Nov 2021Mon 15 Nov 2021Mon 22 Nov 2021Mon 29 Nov 2021Mon 06 Dec 2021Mon 13 Dec 2021
manager 1Project 1APPLE19
Project 2APPLE8
Project 3APPLE4
Project 4APPLE8
Project 5APPLE2
TOTAL30
manager 2Project 1ORANGE
Project 2ORANGE
TOTAL
manager 3Project 1CAKE
Project 2CAKE
TOTAL
manager 4Project 3ORANGE
Project 3CAKE
TOTAL
Manager 5Project 4ORANGE
Project 4CAKE
TOTAL
Manager 6Project 5ORANGE
Project 5CAKE
TOTAL
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

Z
  • Question
Formulae for summing specific cells
Replies
1
Views
120
Peter_SSs
Peter_SSs
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
213
etaf
etaf
R
  • Solved
Highlighting Cells IF Conditions Met
Replies
8
Views
259
awoohaw
awoohaw
K
  • Question
Formula needed to look up specific name and enter data from specific column within same row
Replies
1
Views
191
awoohaw
awoohaw
Y
  • Question
Conditional formatting
Replies
3
Views
117
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,214,996
Messages
6,122,636
Members
449,092
Latest member
bsb1122

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top