Summing a column based on criteria for 2 other columns

have you tried a pivot table? Also, a couple of links to relevant posts inside

I lean towards using a pivot table, the following links may be closer to what you are looking for though
29302.html
29302.html

Re: have you tried a pivot table? Also, a couple of links to relevant posts inside

The cell I wish to update is on a sheet which uses the information to further populate data stored on this worksheet. I have used DSUM on other worksheets but what I am trying to avoid is setting up a named criteria for each person. The worksheet will have multiple people and multiple time periods.

Re: have you tried a pivot table? Also, a couple of links to relevant posts inside

Mark --

Here is a formula-based solution.

I'll assume the following sample in A1:C2 on Sheet2:

{0.325694444444444,"zora",35489;
0.247222222222222,"zora",35489;
0.204861111111111,"carla",35463}

We have a time in A1, a name in B1, and a date in C1 (so don't worry about the strange numbers).

On Sheet1

in B1 enter: 01-03-01 [ a date in European style ]
in B2 enter: 01-03-01 [ another date ]

These criterion dates are not very imaginative, but they will do for the present purposes.

In A3 enter: zora [ a name of interest ]

You may have many names in A from A3 on.

In B3 enter: =SUMPRODUCT((Sheet2!A1:A3)*(Sheet2!B1:B3=A3)*(Sheet2!C1:C3>=$B$1)*(Sheet2!C1:C3<=$B$2))

Custom format B3 as [h]:mm and copy down this cell's formula as far as needed.

Aladin

Re: have you tried a pivot table? Also, a couple of links to relevant posts inside

Thanks for the quick response. I used the following formula {=SUM((PerData!$R$2:$R$742=B8)*(PerData!$AP$2:$AP$742=$AH$1)*(PerData!$W$2:$W$742))}. PerData is the Name for the detail spreadsheet. I was trying to use the column names is my formula which did not work.

Re: have you tried a pivot table? Also, a couple of links to relevant posts inside

Mark,

Great.

You can transform your array formula into an ordinary one very easily:

=SUMPRODUCT((PerData!$R$2:$R$742=B8)*(PerData!$AP$2:$AP$742=$AH$1)*(PerData!$W$2:$W$742))

Note. The "column names" (or labels) are indeed of no use here. As a matter of fact, I don't use them at all anymore.

Aladin

Re: have you tried a pivot table? Also, a couple of links to relevant posts inside

Aladin

I tried SUMPRODUCT and it works great. Now I can sum up hours from another workbook out on a Network. This is GREAT!! You guys know your stuff.

thanks
Mark