Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=DMIN(database,field,criteria) question about criteria | Excel Discussion (Misc queries) | |||
DCOUNTA Complex Criteria Question | Excel Worksheet Functions | |||
Criteria based sum question | Excel Discussion (Misc queries) | |||
SUMIF Question Criteria | Excel Worksheet Functions | |||
query criteria question | Excel Discussion (Misc queries) |