2 Criteria Summing

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

 

[Pete_UK]

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

 

[Shane Devenshire]

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)

 

[Herbert Seidenberg]

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