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?

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?

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?

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?

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?

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:

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: