formula for sumproduct, including date calculation

[Son]

hi, i need to count the days between two dates for many people and only if multiple criteria are met.

here's what i have:


<table border="0" cellpadding="0" cellspacing="0" width="544"><col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:55pt" height="17" width="73">A</td> <td class="xl24" style="border-left:none;width:48pt" width="64">B</td> <td class="xl24" style="border-left:none;width:53pt" width="71">C </td> <td class="xl24" style="border-left:none;width:64pt" width="85">D </td> <td class="xl24" style="border-left:none;width:53pt" width="71">E</td> <td class="xl24" style="border-left:none;width:48pt" width="64">F</td> <td class="xl24" style="border-left:none;width:87pt" width="116">G</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl29" style="height:12.75pt;border-top:none" height="17">NAME</td> <td class="xl29" style="border-top:none;border-left:none">STATUS</td> <td class="xl29" style="border-top:none;border-left:none">CHILDREN</td> <td class="xl29" style="border-top:none;border-left:none">DATE1</td> <td class="xl29" style="border-top:none;border-left:none">DATE2</td> <td class="xl29" style="border-top:none;border-left:none">DAYS360</td> <td class="xl30" style="border-top:none;border-left:none">desired output</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl26" style="height:12.75pt;border-top:none" height="17">JOHN</td> <td class="xl26" style="border-top:none;border-left:none">MARRIED</td> <td class="xl26" style="border-top:none;border-left:none">1</td> <td class="xl27" style="border-top:none;border-left:none">1/1/2011</td> <td class="xl27" style="border-top:none;border-left:none">1/5/2011</td> <td class="xl24" style="border-top:none;border-left:none">120</td> <td class="xl30" style="border-top:none;border-left:none">30</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">JANE</td> <td class="xl24" style="border-top:none;border-left:none">SINGLE</td> <td class="xl24" style="border-top:none;border-left:none">0</td> <td class="xl28" style="border-top:none;border-left:none">12/1/2011</td> <td class="xl28" style="border-top:none;border-left:none">16/12/2011</td> <td class="xl24" style="border-top:none;border-left:none">334</td> <td class="xl30" style="border-top:none;border-left:none">0</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;border-top:none" height="17">GEORGE</td> <td class="xl24" style="border-top:none;border-left:none">MARRIED</td> <td class="xl24" style="border-top:none;border-left:none">2</td> <td class="xl28" style="border-top:none;border-left:none">1/4/2010</td> <td class="xl28" style="border-top:none;border-left:none">1/1/2012</td> <td class="xl24" style="border-top:none;border-left:none">630</td> <td class="xl30" style="border-top:none;border-left:none">0</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl26" style="height:12.75pt;border-top:none" height="17">HELEN</td> <td class="xl26" style="border-top:none;border-left:none">MARRIED</td> <td class="xl26" style="border-top:none;border-left:none">1</td> <td class="xl27" style="border-top:none;border-left:none">1/5/2011</td> <td class="xl27" style="border-top:none;border-left:none">1/8/2011</td> <td class="xl24" style="border-top:none;border-left:none">90</td> <td class="xl30" style="border-top:none;border-left:none">30</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">CRITERIA1</td> <td class="xl25" style="border-top:none;border-left:none">HAVE 1 CHILD ONLY</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">CRITERIA2</td> <td class="xl25" style="border-top:none;border-left:none">DATE1 >= </td> <td class="xl31" style="border-top:none;border-left:none" align="right">1/4/2011</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt;border-top:none" height="17"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none">CRITERIA3</td> <td class="xl25" style="border-top:none;border-left:none">DATE2 <= </td> <td class="xl31" style="border-top:none;border-left:none" align="right">30/4/2011</td> <td class="xl25" style="border-top:none;border-left:none"> </td> <td class="xl25" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>

In this example, only John and Helen have 1 child and their date1 is >= than the set date 1/4/2011 and their date 2 is <= than the set date 30/4/2011.

I have calculated the days360, but i need not the difference between date1 and date2, just the number of days that fall between the set start and end period (1/4 - 30 /4/2011). So, in this example, would be 30 + 30 = 60 days, which is what i want (a single cell with a formula that gets 60 as its output).

Also, I would much prefer it if i could use one single formula in one cell and not use a help column to do intermediate calculation.

I thought to get a sumproduct formula, which could work, but i don't know how to handle the dates part of the criteria.

any ideas on how this could be approached would be most appreciated!

 

[njimack]

Assuming the following:
No. of children is held in cell J1
Date1 is held in cell J2
Date2 is held in celld J3

=SUMPRODUCT(--($D$2:$D$5>=$J$2),--($E$2:$E$5<=$J$3),--($C$2:$C$5=$J$1))*($J$3-$J$2+1)
(by the way - in your example, the dates for John and Helen don't fall within your criteria)

 

[Son]

thanks for your prompt and accurate reply!

i reverted the < and > signs and now it calculates correctly.

still, my problem isn't solved, because i did not explain very well what i need, which is to take also into account the days that are less than 30, for each person.

so, let's say that Helen has 15/4/2011 as date1. Then, i need the formula to get the following result:

30 days for john and 15 for helen = 45

any ideas???

 

[njimack]

Maybe this:

=SUMPRODUCT(--($D$2:$D$5>=$J$2),--($E$2:$E$5<=$J$3),--($C$2:$C$5=$J$1),($E$2:$E$5-$D$2:$D$5))

 

[Son]

thanks for your reply, but no, unfortunately, when i applied it, i got 0

but i'm not sure if something isn't missing

i mean, the first 3 are the set criteria. So, how does the 4th part blend in the formula? there is no -- or * before the 4th part.

when i research this 4th part, i find it gives approximately the days3260 difference bewteen each date, ie 120/338/640 etc

 

[njimack]

The final part of the formula simply calculates the difference between the 2 dates.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	NAME	STATUS	CHILDREN	DATE1	DATE2	*	*	*	Children:	1
2	JOHN	MARRIED	1	10/04/2011	28/04/2011	*	*	*	Date1	01-Apr
3	JANE	SINGLE	0	12/01/2011	16/12/2011	*	*	*	Date2	30-Apr
4	GEORGE	MARRIED	2	01/04/2010	01/01/2012	*	*	*	*	*
5	HELEN	MARRIED	1	15/04/2011	20/04/2011	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	23
Sheet1

 

[Son]

Neil, excellent! it works! thank you so much!

i don't know why it didn't work earlier, i think it was due to the formatting of my dates.

anyway, one more little thing. You notice that in my table George has 2 children, so he gets left out.

If he had 1 child, so he would fall into the criteria, then the formula does not calculate the corresponding days for George. I believe this is because his date2 is in 2012. So if date1 or date2 transend years, the formula does not seem to work?

any ideas on this?

 

[njimack]

Glad it worked. George isn't included because his Date1 is April 2010, not 2011.

 

[Son]

true, but, in practice, april 2011 is included in George's date1 and date2

still, when i changed his date1 from 1/4/2010 to 1/4/2011, still it didn't work. And i do think that it is because his end date is in 2012, because if i change this too, then it works

 

[njimack]

George will only be included when he meets all the criteria:
Children = 1 (met)
Date1>=1/4/11 (not met)
Date2<=30/4/11 (not met)