Days, Employees, Projects, Sums

[OlYeller21]

Hello!

I'm trying to make a tab that sums data from another tab.

The tab titled "2023" holds a list of all employees (D1:DP) that make the x-axis and Dates in 2023 (A3:A367) that make the y-axis. There's also a cheater column that just shows the 3-letter month of the date in A3:A367. In plain English, this tab is filled out daily by a project manager and the indicate what project each employee was on that day.

The tab titled "Project Sum Sheet" holds a list of months (C5:N5) for the year (2023) found in cells (C2:N2). This repeats for a few years but ignore anything outside of 2023. There's a few cheater rows where I was trying different methods. There's also a list of projects in column A6:A36. There's also a sum column but ignore that for now. In plain English, this tab is supposed to show how many man-days were worked on each project in a given month/year.

I want the cells in the table in the tab Project Sum Sheet to show the count of the number of man-days that were used on a given project (column A) for a given month (row 5) in a given year/tab (row 2).

In case you're not familiar, a man-day is one day of work for one person.

I've been working since 9am to come up with the right combination of countifs and indirects to make this work and I keep failing. I could select ranges manually but I really want this to be a copy/past job.

Any help you can provide would be greatly appreciated.

 

[jorismoerings]

Try something like the good old-fashioned sumproduct, like this:

Book1
	A	B	C	D
1	1-1-2023		0	1
2			2023	2023
3
4
5			jan	feb
6	Thunderstrike		28	19
Project Sum

Cell Formulas
Range	Formula
C2:D2	C2	=YEAR($A$1)
C5:D5	C5	=TEXT(EOMONTH($A$1,C1),"mmm")
C6:D6	C6	=SUMPRODUCT(('2023'!$D$2:$S$62='Project Sum'!$A6)*('2023'!$B$2:$B$62='Project Sum'!C$5))