Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 542
Default Sum values if date is before today

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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Sum values if date is before today

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:D376B11: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!
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Sum values if date is before today

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

--
Gary''s Student - gsnu200902
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Sum values if date is before today

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 542
Default Sum values if date is before today

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Formula for number of days between a date and today BrendaP Excel Discussion (Misc queries) 7 July 2nd 09 07:30 PM
IF TODAY equals date in cell A10, or if TODAY is beyond that date SoupNazi Excel Worksheet Functions 4 April 23rd 07 01:14 AM
MAX figure within a date range as a function of today()'s date irvine79 Excel Worksheet Functions 6 February 20th 07 04:28 PM
SUMIF within date range as a function of today()'s date irvine79 Excel Worksheet Functions 8 August 6th 06 05:55 PM
sum values between today and 6 months prior Qaspec Excel Worksheet Functions 3 January 19th 05 09:17 PM


All times are GMT +1. The time now is 05:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"