Using Countif with Match or something similar?

[Realtreegirl75]

I have a data set in A1:AF22. Where Column A is an employee's name and B:AF are the days of the month. Each Row is a different employee and their work schedule for the month. Each cell is the name of the project the employee worked on that day. Screenshot example below.

I need to count how many days each employee worked on each project.

I'm currently writing out each specific formula for the row, which is incredibly time consuming and if the employee's aren't listed in the same order, its going to return incorrect values. My hope is to use a formula to match the employee name in the first column then use that row to count the project names for totals (so its dynamic enough to adjust to out of order employee names). I've tried Match, Vlookup, Xlookup, Index, Row, ChooseRow....everything I can think of but am having no luck. Does anyone have any suggestions? Also, this will live in a SharePoint excel document so Macro's aren't an option.

 

[Fluff]

How about

=SUMPRODUCT(($A$2:$A$8=$A11)*($B$2:$AF$8=B$10))

Where A11 has the name & B10 has the project.

 

[Realtreegirl75]

Because the cells have a name instead of a number in them, SUMPRODUCT just returns 0. Also, this spills to the size of the full table. I need it to return "3" for Employee 1 on Project 1 for the month.

 

[Fluff]

The fact that the cells have text is irrelevant, the formula should still work.
Also that formula cannot spill, so you may have entered it incorrectly.

Can you post some sample data including the formula you are using?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Realtreegirl75]

Well, I feel like a dope! I missed a set of parenthesis and had a name spelled wrong. Fixing those made it work. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.