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!

 

[Son]

i understand now, but isn't there a way around this?

 

[njimack]

i understand now, but isn't there a way around this?

I don't understand. Are you saying you want to ignore the year?

 

[Son]

hmm, not exactly,

what i want is this:

april 2011 falls inside the longer period of 1/4/2010 and 1/1/2012, so i'd like to include these 30 days (april 2011) in my calculation.

but anyway, i've run into another problem as well. If there is no date filled in, I have ---- in my cells. So the date2 - date1 doesn't work because it does not understand the ---- as a date

so, i'm thinking of creating a help table, where only those who meet the criteria would be included, in the hope that this way i can get round both these problems. And then, use your formula to sum the days up.

Neil, thanks again for all your help, i'm very grateful!