sum if w/ multiple criteria + left & frequency functions

[g17]

I need the total number of unique orders for each store on a given day. I tried the formula below but I was getting an error.

SUM(IF(FREQUENCY(IF('Raw Data'!$C$2:$C$256="A", IF(LEFT('Raw Data'!$B$2:$B$256,10)+0=A2),MATCH('Raw Data'!$A$2:$A$256,'Raw Data'!$A$2:$A$256,0)),ROW('Raw Data'!$A$2:$A$256)-ROW('Raw Data'!$A$2)+1),1))

Sheet 1

<style>*<!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {mso-number-format:"yyyy\\-mm\\-dd";} --></style><style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {mso-number-format:"yyyy\\-mm\\-dd";} --> </style> <table style="border-collapse: collapse" border="0" cellpadding="0" cellspacing="0" width="225"> <col width="75"> <col span="2" width="75"> <tbody><tr height="13"> <td height="13" width="75">Day (A)</td> <td width="75"></td> <td width="75">Order (B)</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">2011-01-01</td> <td class="xl24"></td> <td></td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">2011-01-02</td> <td class="xl24"></td> <td></td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">2011-01-03</td> <td class="xl24"></td> <td></td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">2011-01-04</td> <td class="xl24"></td> <td></td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">2011-01-05</td> <td class="xl24"></td> <td></td> </tr> </tbody></table>

Raw Data

<style>*<!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {text-align:center;} --> </style> <table style="border-collapse: collapse" border="0" cellpadding="0" cellspacing="0" width="486"> <col style="mso-width-source:userset;mso-width-alt:4022" width="110"> <col style="mso-width-source:userset;mso-width-alt:7606" width="208"> <col style="mso-width-source:userset;mso-width-alt:3108" width="85"> <col style="mso-width-source:userset;mso-width-alt:3035" width="83"> <tbody><tr height="13"> <td class="xl24" height="13" width="110">Order No (A)</td> <td class="xl24" width="208">Order Date (B)</td> <td class="xl24" width="85">Store (C)
</td> <td class="xl24" width="83">Qty (D)</td> </tr> <tr height="13"> <td class="xl24" height="13">102-765432</td> <td class="xl24">2011-01-30T16:35:23+00:00</td> <td class="xl24">A</td> <td class="xl24">1</td> </tr> <tr height="13"> <td class="xl24" height="13">103-890123</td> <td class="xl24">2011-01-30T03:18:37+00:00</td> <td class="xl24">W</td> <td class="xl24">1</td> </tr> <tr height="13"> <td class="xl24" height="13">104-012345</td> <td class="xl24">2011-01-29T21:06:33+00:00</td> <td class="xl24">W</td> <td class="xl24">2</td> </tr> <tr height="13"> <td class="xl24" height="13">104-012345</td> <td class="xl24">2011-01-29T21:06:33+00:00</td> <td class="xl24">W</td> <td class="xl24">2</td> </tr> <tr height="13"> <td class="xl24" height="13">104-012345</td> <td class="xl24">2011-01-29T21:06:33+00:00</td> <td class="xl24">W</td> <td class="xl24">2</td> </tr> <tr height="13"> <td class="xl24" height="13">103-345678</td> <td class="xl24">2011-01-29T20:10:18+00:00</td> <td class="xl24">A</td> <td class="xl24">1</td> </tr> <tr height="13"> <td class="xl24" height="13">105-123456</td> <td class="xl24">2011-01-29T16:31:23+00:00</td> <td class="xl24">A</td> <td class="xl24">1</td> </tr> <tr height="13"> <td class="xl24" height="13">102-456789</td> <td class="xl24">2011-01-29T12:56:33+00:00</td> <td class="xl24">A</td> <td class="xl24">1</td> </tr> <tr height="13"> <td class="xl24" height="13">103-456789</td> <td class="xl24">2011-01-29T00:37:08+00:00</td> <td class="xl24">A</td> <td class="xl24">1</td> </tr> <tr height="13"> <td class="xl24" height="13">103-012345</td> <td class="xl24">2011-01-28T19:15:31+00:00</td> <td class="xl24">W</td> <td class="xl24">1</td> </tr> <tr height="13"> <td class="xl24" height="13">104-890123</td> <td class="xl24">2011-01-28T18:51:01+00:00</td> <td class="xl24">A</td> <td class="xl24">1</td> </tr> </tbody></table>
help anyone? thanks!

-g

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF('Raw Data'!$C$2:$C$256="A", IF(LEFT('Raw Data'!$B$2:$B$256,10)+0=A2,MATCH('Raw Data'!$A$2:$A$256,'Raw Data'!$A$2:$A$256,0))),ROW('Raw Data'!$A$2:$A$256)-ROW('Raw Data'!$A$2)+1),1))

Note, however, if the dates in Sheet1 are actually text values, not true date values, try removing...

+0

...from your formula.

 

[g17]

it works! thanks! i get so confused w/ the parentheses.

-g

 

[Domenic]

You're welcome! Thanks for the feedback!