DSUM - SUMIF help needed.....

  • Thread starter Thread starter ron_mase
  • Start date Start date
R

ron_mase

I have a spreadsheet that tracks the time that employees spend on
various projects (this gets billed to customers). It looks something
like this:

EMPLOYEE NAME | DATE | PROJECT | COST CATEGORY | HOURS


I am now trying to create a report that will sum the hours worked for
each project and each employee during specific time frames. I'd like
the report to look something like this:

NAME | START DATE | END DATE | PROJECT A | PROJECT B
Joe Smith
Bob Jones
Sally Long

I want to be able to change the dates and have numbers for Project A &
B automatically populate.

What formula do I need to use?

I realize that a databse will be more effective in the long run, but
for the time being, I am stuck with Excel.

TIA

Ron
 
Hi

=VLOOKUP(--(Date>=StartDate),--(Date<=EndDate),--(Project="Project
A"),--(EmployeeName=Name),Hours)

where Date, Project, EmployeeName and Hours are data ranges (they all MUST
be of same dimension) in projects tracing table, and StartDate, EndDate and
Name are conditional values in report table, does returm summary hours spent
on project A with given conditions.


Arvi Laanemets
 
I think you mean SUMPRODUCT not VLOOKUP Arvi.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Of course! Sorry!


Arvi Laanemets


Bob Phillips said:
I think you mean SUMPRODUCT not VLOOKUP Arvi.

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Thank you for the quick replies, but I'm still having some trouble

I typed in the formula and got a result of 0??

I have tried it as:
=SUMPRODUCT((DB!A2:E65536>=B4),(DB!A2:E65536<=C4),(DB!
A2:E65536="ProjectA"),(DB!A2:E65536="Smith, Joe"),DB!A2:E65536)

and as:
=SUMPRODUCT(--(DB!A2:E65536>=B4),--(DB!A2:E65536<=C4),--(DB!
A2:E65536="ProjectA"),--(DB!A2:E65536="Smith, Joe"),DB!A2:E65536)


b4 = start date
c4 = end date
DB!A2:E65536 = Data Range

DB Worksheet looks like this:
A | B | C | D | E
NAME | DATE | PROJECT | COST CATEGORY | HOURS
1 Joe | 1/1/05 | Project A | ADMIN | 4.00
2 Bob | 1/3/05 | Project B | Clincal | 8.00
3
4




I have never used SUMPRODUCT before, but I guess that each section of
this is an arguement that must = TRUE. However, I don't see where the
hours are being added?

Your help is greatly appreciated.

Ron
 
Hi


Thank you for the quick replies, but I'm still having some trouble

I typed in the formula and got a result of 0??

I have tried it as:
=SUMPRODUCT((DB!A2:E65536>=B4),(DB!A2:E65536<=C4),(DB!
A2:E65536="ProjectA"),(DB!A2:E65536="Smith, Joe"),DB!A2:E65536)
Click to expand...



=SUMPRODUCT((DB!$B$2:4B$65536>=B4),(DB!$B$2:$B$65536<=C4),(DB!$C$2:$C$65536=
"Project A"),(DB!$A$2:$A$65536="Joe"),DB!$E$2:$E$65536)


NB! Project name in formula MUST be exactly same as in database - no
additional or missing spaces etc. The same for names - when in database you
have "Joe", then you must search for "Joe", not for "Smith, Joe".

PS. Maybe you'll consider defining all database columns, used in SUMPRODUCT,
as dynamic named ranges. It'll make the formula nicer to look at, and Excel
hasn't use all 65536 rows in calculations (read - it works faster).

Arvi Laanemets
 
Sorry, A typo there

=SUMPRODUCT((DB!$B$2:$B$65536>=B4),(DB!$B$2:$B$65536<=C4),(DB!$C$2:$C$65536=
"Project A"),(DB!$A$2:$A$65536="Joe"),DB!$E$2:$E$65536)


Arvi Laanemets
 
What about the double unary ?

=SUMPRODUCT(--(DB!$B$2:$B$65536>=B4),--(DB!$B$2:$B$65536<=C4),--(DB!$C$2:$C$
65536=
"Project A"),--(DB!$A$2:$A$65536="Joe"),DB!$E$2:$E$65536)
 
I finally got the formula working. Here is the working formula for
those that care:

=SUMPRODUCT((DB!$B$2:$B$65536>=$B$2)*(DB!$B$2:$B$65536<=$C$2)*(DB!$C$2:$C$65536=B$4)*(DB!$A$2:$A$65536=$A5),(DB!$E$2:$E$65536))

b2 = Begin Date
c2 = End Date
b4 = Project Name
a5 = Employee Name

Thanks to all those that helped me learn something new today.

RON
 
Back
Top