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.

You could think about using a pivot table, but if you require a
formula solution then you can put this in B2:

=SUMPRODUCT((source!$B$1:$B$1000=$A2)*(source!$E$1:$E$1000=B$1)*
(source!$F$1:$F$1000))

Then copy across and down as required.

This will look for exact matches with the entries on row 1, so if you
want sub-string matches you can change it to:

=SUMPRODUCT((source!$B$1:$B$1000=$A2)*(ISNUMBER(SEARCH(B$1,source!$E
$1:$E$1000)))*(source!$F$1:$F$1000))

You can only use SUMIF for a single condition.

Hope this helps.

Pete

On Feb 23, 6:02*pm, CurtPDX <Curt...@discussions.microsoft.com> wrote:
> 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.

Hi,

In 2007 you can write

=SUMIFS(source!$F$1:$F$1000, source!$B$1:$B$1000,$A2,source!$E$1:$E$1000,B$1)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"CurtPDX" wrote:

> 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.

Excel 2007 Pivot Table
No formulas needed.
Includes "string contains" truth function and chart:
http://www.mediafire.com/file/y1ie43mnmny/02_24_09.xlsx