Question about using SUM with a date criteria
 

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

Question about using SUM with a date criteria
 

You should tell us more about what your data looks like
Meanwhile look into SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Michael Slater" wrote in message
...
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

Question about using SUM with a date criteria
 

Try

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

confirmed with CTRL+SHIFT+ENTER

"Bernard Liengme" wrote:

You should tell us more about what your data looks like
Meanwhile look into SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Michael Slater" wrote in message
...
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

Question about using SUM with a date criteria
 

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



"daddylonglegs" wrote:

Try

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

confirmed with CTRL+SHIFT+ENTER

"Bernard Liengme" wrote:

You should tell us more about what your data looks like
Meanwhile look into SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Michael Slater" wrote in message
...
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