Sum values that meet certain criteria

[vagabond225]

Hi,

I have a spreadsheet that is set out to look similar to a MS Project plan, with dates across the top, users in column A and Assignments in column B. In the corresponding cell that matches the user, date and assignment they will enter the number of hours they anticipate working on the assignment.

I then have a summary sheet that covers all assignments, week ending dates and users. I want to be able to calculate the hours and copy it into the cell in the summary sheet that matches the Day, User and Assignment. I have tried using SUMIF, but this requires the criteria cells to all be in columns, but with this sheet the dates are in a row.

Can anyone please help with this?

Regards,

Adam

 

[Aladin Akyurek]

Hi,

That is perfect. Thank you very, very much. Just what i needed.

Cheers,

Adam

Just a small note:

There is no need for the -- bit in:

=SUMPRODUCT(
    ('Project & Risk Mgmt'!$A$3:$A$20='March 2009'!$A7)*
    ('Project & Risk Mgmt'!$B$3:$B$20='March 2009'!D$4)*
    ('Project & Risk Mgmt'!$C$1:$H$1='March 2009'!D$5),
    ('Project & Risk Mgmt'!$C$3:$H$20))

 

[Stormseed]

There is no need for the -- bit in

I agree, that the use of double unary would be redundant ( I plonked it in just for the heck of it ).
However, what would be the difference ? would it serve better as far as the speed of calculation is concerned ?

 

[Aladin Akyurek]

I agree, that the use of double unary would be redundant ( I plonked it in just for the heck of it ).
However, what would be the difference ? would it serve better as far as the speed of calculation is concerned ?

http://www.mrexcel.com/forum/showthread.php?t=202204

http://www.mrexcel.com/forum/showthread.php?t=70547

http://www.mrexcel.com/wwwboard/messages/8961.html

 

[Stormseed]

Hi again, Aladin

Albeit, post your detailed explanation, since the Date in Excel is already considered as a number, I do not need to coerce it with the double unary, correct ?

Also, as I said earlier, I just put those for the heck of it. I also wanted to know if that could make any difference as long as the performance is concerned ?

Thank you very much, Aladin.