Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif date range
We track attendance per individual person on one worksheet. We only enter the
dates that the person is out. I want a SUMIF formula that will only tell me the total sum of columnB between a certain range of dates. For example: for October this person was out 2 days (of a total 4.5 days out). Date Absent Full/Half Day 8/31/07 1.0 9/4/07 0.5 9/24/07 1.0 10/01/07 0.5 10/25/07 1.0 10/26/07 0.5 Networkdays will count the number of days between two days, but won't total half days. Is there any formula that will do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif date range
Try this:
=SUMPRODUCT(--(TEXT(A2:A7,"m/yyyy")="10/2007"),B2:B7) -- Biff Microsoft Excel MVP "Delsy" wrote in message ... We track attendance per individual person on one worksheet. We only enter the dates that the person is out. I want a SUMIF formula that will only tell me the total sum of columnB between a certain range of dates. For example: for October this person was out 2 days (of a total 4.5 days out). Date Absent Full/Half Day 8/31/07 1.0 9/4/07 0.5 9/24/07 1.0 10/01/07 0.5 10/25/07 1.0 10/26/07 0.5 Networkdays will count the number of days between two days, but won't total half days. Is there any formula that will do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif date range
If you enter the Month Number 1-12 in B1 then
=SUMPRODUCT(--(MONTH($A$4:$A$17)=B1)*($B$4:$B$17)) Regards Peter "Delsy" wrote: We track attendance per individual person on one worksheet. We only enter the dates that the person is out. I want a SUMIF formula that will only tell me the total sum of columnB between a certain range of dates. For example: for October this person was out 2 days (of a total 4.5 days out). Date Absent Full/Half Day 8/31/07 1.0 9/4/07 0.5 9/24/07 1.0 10/01/07 0.5 10/25/07 1.0 10/26/07 0.5 Networkdays will count the number of days between two days, but won't total half days. Is there any formula that will do this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif date range
THANKYOU!!!! This was exactly what I needed.
"T. Valko" wrote: Try this: =SUMPRODUCT(--(TEXT(A2:A7,"m/yyyy")="10/2007"),B2:B7) -- Biff Microsoft Excel MVP "Delsy" wrote in message ... We track attendance per individual person on one worksheet. We only enter the dates that the person is out. I want a SUMIF formula that will only tell me the total sum of columnB between a certain range of dates. For example: for October this person was out 2 days (of a total 4.5 days out). Date Absent Full/Half Day 8/31/07 1.0 9/4/07 0.5 9/24/07 1.0 10/01/07 0.5 10/25/07 1.0 10/26/07 0.5 Networkdays will count the number of days between two days, but won't total half days. Is there any formula that will do this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif date range
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Delsy" wrote in message ... THANKYOU!!!! This was exactly what I needed. "T. Valko" wrote: Try this: =SUMPRODUCT(--(TEXT(A2:A7,"m/yyyy")="10/2007"),B2:B7) -- Biff Microsoft Excel MVP "Delsy" wrote in message ... We track attendance per individual person on one worksheet. We only enter the dates that the person is out. I want a SUMIF formula that will only tell me the total sum of columnB between a certain range of dates. For example: for October this person was out 2 days (of a total 4.5 days out). Date Absent Full/Half Day 8/31/07 1.0 9/4/07 0.5 9/24/07 1.0 10/01/07 0.5 10/25/07 1.0 10/26/07 0.5 Networkdays will count the number of days between two days, but won't total half days. Is there any formula that will do this? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif date range
What if we needed to track the number of days out between a specific date range? For example how many days was this person out from October 15th - November 10th?
On Monday, October 29, 2007 4:01 PM Dels wrote: We track attendance per individual person on one worksheet. We only enter the dates that the person is out. I want a SUMIF formula that will only tell me the total sum of columnB between a certain range of dates. For example: for October this person was out 2 days (of a total 4.5 days out). Date Absent Full/Half Day 8/31/07 1.0 9/4/07 0.5 9/24/07 1.0 10/01/07 0.5 10/25/07 1.0 10/26/07 0.5 Networkdays will count the number of days between two days, but won't total half days. Is there any formula that will do this? On Monday, October 29, 2007 4:35 PM T. Valko wrote: Try this: =SUMPRODUCT(--(TEXT(A2:A7,"m/yyyy")="10/2007"),B2:B7) -- Biff Microsoft Excel MVP On Monday, October 29, 2007 4:51 PM BillyLidde wrote: If you enter the Month Number 1-12 in B1 then =SUMPRODUCT(--(MONTH($A$4:$A$17)=B1)*($B$4:$B$17)) Regards Peter "Delsy" wrote: |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif date range
You got lucky. You should have started a new post, instead of adding to 4 year old post.
With the beginning date in D2 and the ending date in E2 then... =SUMIF(A2:A7,"="&D2,B2:B7)-SUMIF(A2:A7,""&E2,B2:B7) '--- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (XL Companion add-in: compares, matches, counts, lists, finds, deletes...) "Delsy Hughes" wrote in message ... What if we needed to track the number of days out between a specific date range? For example how many days was this person out from October 15th - November 10th? On Monday, October 29, 2007 4:01 PM Dels wrote: We track attendance per individual person on one worksheet. We only enter the dates that the person is out. I want a SUMIF formula that will only tell me the total sum of columnB between a certain range of dates. For example: for October this person was out 2 days (of a total 4.5 days out). Date Absent Full/Half Day 8/31/07 1.0 9/4/07 0.5 9/24/07 1.0 10/01/07 0.5 10/25/07 1.0 10/26/07 0.5 Networkdays will count the number of days between two days, but won't total half days. Is there any formula that will do this? On Monday, October 29, 2007 4:35 PM T. Valko wrote: Try this: =SUMPRODUCT(--(TEXT(A2:A7,"m/yyyy")="10/2007"),B2:B7) -- Biff Microsoft Excel MVP On Monday, October 29, 2007 4:51 PM BillyLidde wrote: If you enter the Month Number 1-12 in B1 then =SUMPRODUCT(--(MONTH($A$4:$A$17)=B1)*($B$4:$B$17)) Regards Peter "Delsy" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif for Date Range | Excel Discussion (Misc queries) | |||
Sumif with two criteria including a date range | Excel Discussion (Misc queries) | |||
SUMIF within date range as a function of today()'s date | Excel Worksheet Functions | |||
Sumif and a range | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |