SumIf or CountIf? Or Something Else??

[Mae]

Using Excel 2000 on Win2000

I have a workbook that is composed of weekly timesheets.
Each sheet has dates across the top, then rows of projects
and hours worked. There is also a row to record Paid-Time-
Off hours. Something like this:

Job# 12/29/03 12/30/03 12/31/03 1/1/04 1/2/04 |Total
1001 6 0 0 0 7 | 13
1002 2 0 0 0 1 | 3
PTO 0 8 8 8 0 | 24
----------------------------------------------------
Total 8 8 8 8 8 | 40

I have a PTO Tracking sheet that uses our allotted PTO
days, and subtracts the sum of PTO Total from all sheets,
to come up with PTO Remaining. My problem is, that PTO
resets on January 1. So in the example above, the PTO
Total for the week ending 1/2/04 is 24 hours, however,
only 8 of the hours are in 2004. I can't divide it up
into separate '03 and '04 sheets.

Is there a function I can use to say "Sum PTO hours if the
Date for those hours is > 12/31/03"?? I tried to get this
using SumIf, but I'm not sure that's appropriate. I
couldn't get it to figure out which date went with which
number.

Any ideas???
Thank you!

 

[Norman Harker]

Hi Mae!

Here's a syntax that works:

=SUMIF(A1:A10,">31-dec-2003",B1:B10)

But perhaps better to put the date in another cell and use:

=SUMIF(A1:A10,">"&G1,B1:B10)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Mae]

Thanks, that worked! I had to change it to reflect that
I'm summing across rows, rather than down columns, but
that was easy enough.

Just in case anyone has a similar problem, I'll show what
I ended up doing. Dates are in cells C5:I5, and each
day's Paid-Time-Off is recorded in cells C29:I29. J29 is
the PTO total for each week.

=SUM('Sheet1:LastSheet'!J29)-SUMIF('Sheet1'!
C5:I5,"<1/1/04",'1-4'!C29:I29)

This formula says "Sum up all the totals of J29, from the
first sheet to the last. Then look at the first sheet
again and if the date is less than 2004, add up any PTO
recorded for those dates. Subtract the 2003 hours from
the total sum to get your PTO Hours Used in 2004."

Thanks again!

 

[Norman Harker]

Hi Mae!

Thanks for thanks and for sharing the results of your efforts.

A third way of entering that pesky date is:

=SUMIF(A1:A10,">"&DATE(2003,12,31),B1:B10)

But don't ask why the need to concatenate the > with the DATE function


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Mae]

Oops, a typo. The formula should read:

=SUM('Sheet1:LastSheet'!J29)-SUMIF('Sheet1'!
C5:I5,"<1/1/04",'Sheet1'!C29:I29)