Aggregating Columns In Excel

Question

I have data consisting of a date and a list of purchases, something like this (commas represent separate columns):

**date,product1,product2,product3** 
08/11/13,oranges,apples,chips
08/11/13,grapes,oranges
08/12/13,grapes,pineapples
08/12/13,grapes,oranges

I want to know how many items of each product were purchased on each date (and plot it).

If I had a single product column it would be pretty simple; I could just use a pivot table. However in my case, for August 11th, I need a way of counting the "oranges" from product1 together with the "oranges" from product2...

One way would be to duplicate rows and translate the data to a single column, but I'd like to avoid it.

Is there a way to do that, preferably without the use of VBA?

Answer 1

If your data are in the range A2:D25, then you could use the following array formula:

=SUM(($A$2:$A$25=DATEVALUE("8/11/13"))*
    (($B$2:$B$25="oranges")+($C$2:$C$25="oranges")+($D$2:$D$25="oranges")))

You will need to enter it with the Control-Shift-Enter key combination.

If you have several dates for which you want to calculate the totals for each of a large number of products, you could do the following.

Let's assume that your product data are in the range A2:D20000 in Sheet 1, with the date range in column A, as in your example. Say you want to put the product counts in Sheet2.

In Sheet2, you would enter into cells B1, C1, etc., the dates you are interested in. Below that, in the range A2:A2000, you have the list of your products. In cell B2, enter this array formula:

=SUM((Sheet1!$A$2:$A$20000=B$1)*((Sheet1!$B$2:$B$20000=$A2)+
     (Sheet1!$C$2:$C$20000=$A2)+(Sheet1!$D$2:$D$20000=$A2)))

and copy it down to the bottom of the list of products. That gives you the counts for the first date.

Next, copy that whole column of product counts across for each column that has a date in row 1. That gives you the counts for all the dates.

Alternatively, if you have both a large number of products and a large number of dates, an only slightly more complicated non-array formula will give you better performance (i.e., will take less time to calculate).

In cell B2, you would instead enter:

     =COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$B$2:$B$20000,$A2)+
      COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$C$2:$C$20000,$A2)+
      COUNTIFS(Sheet1!$A$2:$A$20000,B$1,Sheet1!$D$2:$D$20000,$A2)

Then, you'd copy the formula down to the bottom of the products list, and then copy that whole column of formulas (in the range B2:B2000) across all the columns for which you've put dates in row 1.