Question about using SUM with a date criteria

  • Thread starter Thread starter Michael Slater
  • Start date Start date
M

Michael Slater

Hello,

I'm using the following array formula (Thank you again Macropod!) to add
Sick/Vacation/Personal leave hours (as simple numbers) in cells that also
contain text:

=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))

Is there a way to add a date criteria to this formula so that it will only
add those hours as of the current date? In the spreadsheet, Column A
contains dates.

Any help is appreciated.

Thanks,

Mike
 
Try

=SUM(IF(A1:A4=TODAY(),LEFT(0&B1:B4,FIND(" ",B1:B4&" "))+0))

confirmed with CTRL+SHIFT+ENTER
 
Thank you for your suggestions! Daddylonglegs, I was not able to get your
formula to work, however, I did figure out how to modify the original formula
I was using:

=SUM(IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1))))

Modification:

=SUM(IF(A1:A4<=TODAY(),IF(ISERROR(VALUE(LEFT(B1:B4,FIND("
",B1:B4)-1))),0,VALUE(LEFT(B1:B4,FIND(" ",B1:B4)-1)))))

Again, Thanks for all your help!

Mike
 
Back
Top