Hi,

I have dates in B11:B376 and numbers representing time taken off in D11:D376
(ie. 1.0 for a full day, and 0.5 for half day vacation taken)

My question is how do I sum the time taken if the date has already passed,
ie. if the date in column B is less than TODAY()

Thanks in advance

Hi there!

To sum the time taken if the date has already passed, you can use the SUMIFS function in Excel. Here's how you can do it:

1. Select an empty cell where you want to display the result.
2. Type the following formula:
   Formula:

   =SUMIFS(D11:D376, B11:B376, "<"&TODAY()) 

3. Press Enter.

This formula will sum the values in column D (time taken) if the corresponding date in column B is less than today's date.

Let me explain the formula in more detail:

- SUMIFS: This is the function that will sum the values based on the criteria you specify.
- D11:D376: This is the range of values you want to sum.
- B11:B376: This is the range of dates you want to use as criteria.
- "<"&TODAY(): This is the criteria you want to use. The "<" symbol means "less than", and "&TODAY()" means today's date. So, the formula will only sum the values in column D if the corresponding date in column B is less than today's date.

Hope this helps!

=SUMPRODUCT((B11:B376<TODAY())*(D11:D376))

--
Gary''s Student - gsnu200902

Try
=SUMIF(B11:B376,"<" & TODAY(),D11:D376)

If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

Hi,

I have dates in B11:B376 and numbers representing time taken off in D11:D376
(ie. 1.0 for a full day, and 0.5 for half day vacation taken)

My question is how do I sum the time taken if the date has already passed,
ie. if the date in column B is less than TODAY()

Thanks in advance

thank you for the replies

Gary''s Student:
=SUMPRODUCT((B11:B376<TODAY())*(D11:D376)) worked for some cases but I got
an error (#Value) in other cases. could not figure out what was messing it
up. I believe you get the #value when you have different sized arrays, not
sure why it was doing this. thanks you though

Jacob:
that formula worked great. Exactly what i was looking for. Thanks again.

"Jacob Skaria" wrote:

Try
=SUMIF(B11:B376,"<" & TODAY(),D11:D376)

If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

Hi,

I have dates in B11:B376 and numbers representing time taken off in D11:D376
(ie. 1.0 for a full day, and 0.5 for half day vacation taken)

My question is how do I sum the time taken if the date has already passed,
ie. if the date in column B is less than TODAY()

Thanks in advance