SUMIF to calculate Number of Days between Dates in 2 Columns Meeting Criterial

[eveready]

Hi, Please help

I have 2 Columns of Holiday Dates (Column A is the "FROM" date; and Column B is the 'TO' date) and Column C is Peoples Names corresponding to the Holiday dates:

ie:

COLUMN 1 ; COLUMN 2 ; COLUMN 3
A1= 2 Jan 13 ; B1= 10 Jan 13 ; C1= Tom
A2= 4 May 13 ; B2= 10 Jun 13 ; C2= John
A3= 7 Jul 13 ; B3= 10 Jul 13 ; C3= Tom
A4= 3 Aug 13 ; B4= 25 Aug 13 ; C4= John
A5= 6 Dec 13 ; B5= 15 Dec 13 ; C5= John.

I am trying to figure out a formula (eg SUMIF) to calcluate and summarize the number of Days Holiday based on a person's name (eg Tom's total Number of days Holiday days) based on the above column configuration of Dates and Names.

Please help.

 

[Aladin Akyurek]

Always very helpful if the desired results, calculated manually, are also included. That said, guessing:

E1: Tom
E2: John

F1, control+shift+enter, not just enter, and copy down:

=SUM(IF($A$1:$A$5*$B$1:$B$5,IF($C$1:$C$5=E1,$B$1:$B$5-$A$1:$A$5)))

The following set up might be easier on you:

D1, just enter and copy down:

=IF(A1*B1,B1-A1,0)

Now you can invoke a SumIf formula in F1...

=SUMIF($C$1:$C$5,E1,$D$1:$D$5)

 

[AlphaFrog]

Try something like this.

=SUMPRODUCT((B1:B5-A1:A5+1),--(C1:C5="Tom"))

You can replace "Tom" with a cell reference that contains the value "Tom"

The +1 means the dates are inclusive e.g. Jan 1 to Jan 2 is two days.

 

[Sachin2k6]

Put in Column 4
=B1-A1

Put in Column 5

=SumIf(C1:C5,"Tom",D1:D5)

 

[eveready]

MANY THANKS ALL RESPONDERS


I went for the simplest looking response and ALPHAFROG'S worked a treat.

Many thanks Alphafrog.

Regards

Try something like this.

=SUMPRODUCT((B1:B5-A1:A5+1),--(C1:C5="Tom"))

You can replace "Tom" with a cell reference that contains the value "Tom"

The +1 means the dates are inclusive e.g. Jan 1 to Jan 2 is two days.