Count upon some conditions

[Gildasl]

Hi,

I need to count the number of days that at least one red umbrella is sold and group them between 2 dates (every 2 weeks).
In the case below:
1 day at least some umbrella have been sold between 6/15/2011 and 6/30/2001
2 days at least some umbrella have been sold between 8/1/2011 and 8/15/2011.

Thanks,
Gildas


<table border="0" cellpadding="0" cellspacing="0" width="358"><colgroup><col style="mso-width-source:userset;mso-width-alt:5595;width:115pt" width="153"> <col style="mso-width-source:userset;mso-width-alt:7497;width:154pt" width="205"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:115pt" height="20" width="153">Creation date</td> <td class="xl66" style="width:154pt" width="205">Number of sales of Red umbrella</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">6/28/2011</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">6/28/2011</td> <td class="xl66" align="right">93</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">7/8/2011</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">7/21/2011</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/2/2011</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/4/2011</td> <td class="xl66" align="right">5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/5/2011</td> <td class="xl66" align="right">2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/5/2011</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/8/2011</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/10/2011</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/15/2011</td> <td class="xl66" align="right">120</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/16/2011</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20" align="right">8/16/2011</td> <td class="xl66">
</td> </tr> </tbody></table>

 

[Robert Mika]

Shuould not the answer for the second line be 3?
4,5, and 15 of August?

 

[jono19872006]

try something like this, if the cells are formatted right this should work, note you can also change the date for a cell if you wanted to make it easier ie SUMPRODUCT(--((A2:A14)>D1..... etc


SUMPRODUCT(--((A2:A14)>6/15/2011),--((A2:A14)<6/30/2011),--(B2:B14>0))

 

[Gildasl]

@ jono19872006
Your proposal do not work because the array arguments do not have the same dimensions (according to Excell help).

BTW, why is the meaning of the sign --?

Thanks,
Gildas

 

[Gildasl]

Good catch Robert. Answer is 3 for the second line.

 

[bstory84]

Try this.

<?XML:NAMESPACE PREFIX = D1),$B$2 /><D1),$B$2:$B$14)< u>sorry my posts keep cuttinf off the formula</D1),$B$2:$B$14)<>
<D1),$B$2:$B$14)< u>

</D1),$B$2:$B$14)<>
<D1),$B$2:$B$14)< u>'=SUMPRODUCT(--(($A$2:$A$14)>C1),--(($A$2:$A$14)<D1),$B$2:$B$14)< D1),$B$2:$B$14)<>
<D1),$B$2:$B$14)< u>

</D1),$B$2:$B$14)<>
<D1),$B$2:$B$14)< u>Ignore this post. formula gets cut off past the > sign</D1),$B$2:$B$14)<>
</D1),$B$2:$B$14)<></D1),$B$2:$B$14)<>

 

[bstory84]

Use jono19872006 formula just get rid of ,--(B2:B14)>0
it will count the number of true matches.

 

[T. Valko]

Hi,

I need to count the number of days that at least one red umbrella is sold and group them between 2 dates (every 2 weeks).
In the case below:
1 day at least some umbrella have been sold between 6/15/2011 and 6/30/2001
2 days at least some umbrella have been sold between 8/1/2011 and 8/15/2011.

Thanks,
Gildas


<TABLE cellSpacing=0 cellPadding=0 width=358 border=0><COLGROUP><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="WIDTH: 115pt; HEIGHT: 15pt" width=153 height=20>Creation date</TD><TD class=xl66 style="WIDTH: 154pt" width=205>Number of sales of Red umbrella</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>6/28/2011</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>6/28/2011</TD><TD class=xl66 align=right>93</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>7/8/2011</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>7/21/2011</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/2/2011</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/4/2011</TD><TD class=xl66 align=right>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/5/2011</TD><TD class=xl66 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/5/2011</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/8/2011</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/10/2011</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/15/2011</TD><TD class=xl66 align=right>120</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/16/2011</TD><TD class=xl66>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" align=right height=20>8/16/2011</TD><TD class=xl66>

</TD></TR></TBODY></TABLE>

Try this...

Book1
	A	B	C	D	E	F
2	6/28/2011	_	_	6/15/2011	6/30/2011	1
3	6/28/2011	93	_	8/1/2011	8/15/2011	3
4	7/8/2011	_	_	_	_	_
5	7/21/2011	_	_	_	_	_
6	8/2/2011	_	_	_	_	_
7	8/4/2011	5	_	_	_	_
8	8/5/2011	2	_	_	_	_
9	8/5/2011	_	_	_	_	_
10	8/8/2011	_	_	_	_	_
11	8/10/2011	_	_	_	_	_
12	8/15/2011	120	_	_	_	_
13	8/16/2011	_	_	_	_	_
14	8/16/2011	_	_	_	_	_
Sheet1

One of these formulas entered in F2 and copied down...

If you're using Excel 2007 or later:

=COUNTIFS(A$2:A$14,">="&D2,A$2:A$14,"<="&E2,B$2:B$14,"<>")

This one will work in all versions of Excel:

=SUMPRODUCT(--(A$2:A$14>=D2),--(A$2:A$14<=E2),--(B$2:B$14<>""))

 

[Gildasl]

=SUMPRODUCT(--(A$2:A$14>=D2),--(A$2:A$14<=E2),--(B$2:B$14<>"")) works fine.

BTW, I have not figured out yet the meaning of the -- sign. Ideas?
Thanks,

 

[T. Valko]

=SUMPRODUCT(--(A$2:A$14>=D2),--(A$2:A$14<=E2),--(B$2:B$14<>"")) works fine.

Good deal!

BTW, I have not figured out yet the meaning of the -- sign. Ideas?
Thanks,

See this...

http://xldynamic.com/source/xld.SUMPRODUCT.html