Sum cells using criteria from a row and a column

[Raul]

I am trying to sum cells in a sheet based on matching criteria in a row and
matching criteria in a column.

The data that I am working with is represented as:

Resource: Resource1 JAN JAN JAN JAN FEB
Project Project Description 12/19 12/26 1/2 1/9 1/16
Admin Administration 10 10 10
10 10
Holiday Holiday 8
8
Other Other time Off
Training Training 20
Vacation Vacation 22 30 10 30 22

I am looking to create a separate sheet that is a summary of this
information.
I want to match on the project (column A) and the Month (Row 1), where the
project from the detail resource sheet matches the project from the summary
sheet and the month from the detail sheet matches the month on the summary
sheet, I want to summ the hours.

I have tried several iterations of the SUM(IF), SUMIF, and SUMPRODUCT and I
either get all cells that match a project, regardless of the month or I get
errors in the formula.

Help!!!!!!

 

[Bernie Deitrick]

Raul,

If your month's data is a single column, and the column headings are labels rather than actual
dates:

=SUMIF(Sheet2!A:A,B3,INDEX(Sheet2!1:65536,,MATCH(B4,Sheet2!1:1,FALSE)))

Where B3 has the Project, and B4 has the Month entered in a way that matches the column header
labels.

If you have mutliple labels for months, then try array entering (enter using Ctrl-Shift-Enter) a
formula like

=SUM(IF(Sheet2!A1:A100=B3,OFFSET(Sheet2!$A$1,0,MATCH(B4,Sheet2!1:1,FALSE)-1,100,COUNTIF(Sheet2!1:1,B4))))

If you have actual dates in row 1 and in cell B4, then array enter a formula like

=SUM(IF(Sheet2!A1:A100=B3,OFFSET(Sheet2!$A$1,0,MATCH(TEXT(B4,"mmm"),TEXT(Sheet2!A1:H1,"mmm"),FALSE)-1,100,SUMPRODUCT((TEXT(Sheet2!A1:H1,"mmm")=TEXT(B4,"mmm"))*1))))

for which I have assumed that you have 8 columns (A to H) and 100 rows of data.

HTH,
Bernie
MS Excel MVP

 

[Raul]

Thank you. The second example you gave is the situation I have and it worked
out quite well.