C
CurtPDX
I have the following source data:
A1 = name (text)
B1 = start date (as a number displayed in Date format)
E1 = project title (a text string)
F1 = hours (number)
Subsequent rows are in the same format. This source data has ~1000 lines, 8
different project titles and 12 date ranges.
I want to create a summary table as follows:
Rows represent a date range (manually pre-defined)
Columns are for each project
The intersection of a row & column gives the sum of hours for that time
period and that project.
That is, the result looks like the following:
A1 = -- blank --
A2 = date1
A3 = date2
A4 = date3
B2 = project title1 (a string)
C2 = project title2
B2 = sum of hours (from source data) for date1 and project title1
C2 = sum of hours for date1 and project title2
B3 = sum of hours for date2 and project title1
C3 = sum of hours for date2 and project title2
etc.
(In one case the projects are titled "foo sprint 1", "foo sprint 2", "foo
sprints 11-20", etc. I want to aggregate them under "foo sprint" so I'll
need some "string contains" truth function.)
I think I want to use SumIF, but I'm struggling to define the criteria for
this two-way match.
Thanks.
A1 = name (text)
B1 = start date (as a number displayed in Date format)
E1 = project title (a text string)
F1 = hours (number)
Subsequent rows are in the same format. This source data has ~1000 lines, 8
different project titles and 12 date ranges.
I want to create a summary table as follows:
Rows represent a date range (manually pre-defined)
Columns are for each project
The intersection of a row & column gives the sum of hours for that time
period and that project.
That is, the result looks like the following:
A1 = -- blank --
A2 = date1
A3 = date2
A4 = date3
B2 = project title1 (a string)
C2 = project title2
B2 = sum of hours (from source data) for date1 and project title1
C2 = sum of hours for date1 and project title2
B3 = sum of hours for date2 and project title1
C3 = sum of hours for date2 and project title2
etc.
(In one case the projects are titled "foo sprint 1", "foo sprint 2", "foo
sprints 11-20", etc. I want to aggregate them under "foo sprint" so I'll
need some "string contains" truth function.)
I think I want to use SumIF, but I'm struggling to define the criteria for
this two-way match.
Thanks.