## Friday, November 22, 2013

### yogi_Compute Count Of Colors By Date From Data In Columns A And B And Cross Tab Count Of Colors By Color And Date

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 22, 2013
Query function
im really a lazy bastard and dont like to make toooo many formula.. so here i go..

Data on the Table 1. plus the =query formula gives me the result on the right

`=QUERY(A1:C, "select B, sum(C) group by B pivot A label R 'Name'")`
``` ```

Table 1.
 A B C DATE NAME # RESULT ↓↓↓ 16-Nov red 1 16-Nov orange 2 Name 2013-11-16 2013-11-17 2013-11-18 16-Nov blue 3 blue 3 3 2 16-Nov yellow 1 indigo 2 1 17-Nov indigo 2 orange 2 1 3 17-Nov red 3 red 1 3 2 17-Nov orange 1 yellow 1 2 1 17-Nov yellow 2 17-Nov blue 3 18-Nov indigo 1 18-Nov red 2 18-Nov orange 3 18-Nov yellow 1 18-Nov blue 2

but what if there is no column C and the table looks like Table 2.
what (simple) =query formula should i do/use to achieve the same exact result?

Table 2.
 A B DATE NAME 11/16/2013 red 11/16/2013 orange 11/16/2013 blue 11/16/2013 yellow 11/17/2013 indigo 11/17/2013 red 11/17/2013 orange 11/17/2013 yellow 11/17/2013 blue 11/18/2013 indigo 11/18/2013 red 11/18/2013 orange 11/18/2013 yellow 11/18/2013 blue 11/16/2013 orange 11/16/2013 blue 11/16/2013 blue 11/17/2013 indigo 11/17/2013 red 11/17/2013 red 11/17/2013 yellow 11/17/2013 blue 11/17/2013 blue 11/18/2013 red 11/18/2013 orange 11/18/2013 orange 11/18/2013 blue 11/16/2013 orange 11/18/2013 blue 11/16/2013 orange

i find =query really helpful for lazy bastards like me in making a table..
and one more thing, how can i format the date?
been struggling for hours now..