Formula: values in one column based on corresponding data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My goal is simply this - I want to add a series of values (hours and minutes)
worked on specific projects within one months time. Now, the month/day/year
is listed in a separate column. Thus, one column lists all the hours and
minutes used to complete a project and now I want to add up those values for
a specific month's time. The reason is so I can explain how much time was
spent doing a specific task for various cases. I believe the formula starts
with "=sumif(" but after that I simply do not know.
 
SUMIF won't suffice as you have multiple conditions to satisfy for the
summing, from my interp on your posting

Try SUMPRODUCT instead, configured along these lines:

Assume projects are listed within A2:A100, eg: Project1, Project2, etc, real
dates listed in B2:B100, eg: 1-Jan-07, 3-Feb-07, etc, and the required
quantity to be summed, viz the "hours and minutes" listed in C2:C100 (these
are assumed real times)

Put in say, D2:
=SUMPRODUCT((A2:A100="Project1")*(TEXT(B2:B100,"mmmyy")="Jan07")*C2:C100)
Format D2 as time to taste. D2 will return the required total time spent for
Project1 in January 2007. Modify to suit. Note that entire col ranges cannot
be used, and ranges should be identically sized.
 
Back
Top