Combining value count from multiple columns in pivot table

[AFsimRA]

Hello everyone. I'm new here. I've been scouring the threads for an answer to this and I apologize if it's already been explained.

My issue is that I have a very large data set where the same value may occur in multiple columns. I need to create a pivot chart, or possibly find another solution, that allows me to track the number of times the value occurs within a date range.

Something like this but I need it to tell me total # of each fruit purchased for that month.

Shopper date product 1 product 2 product 3
jim 1/31/2013 apple banana orange
jill 2/13/2013 banana orange apple
bob 2/4/2013 apple orange banana
jill 1/31/2013 apple banana null
joe 2/13/2013 orange apple banana
betty 2/4/2013 banana orange apple
claire 1/31/2013 apple null null
bob 2/13/2013 banana null null


Here is the example data file.

Thank you in advance. Any assistance is appreciated.
pivot grouping example.xlsx

[swhitesell]

A better solution may be the use of the CountIf function. Attached is your spreadsheet with the example of this function.

pivot_grouping_example(solution).xlsx

[AFsimRA]

I was thinking that might be the case...
Unfortunately in my real database, which I can't share due to sensitivity, there could be hundreds of product types. It will take a while to set up countif and try to account for all unique values. Also I need to group the results by month.
Is there anything else that might work where it will classify all matching values without my having to specify?

[swhitesell]

You are at the point where it almost makes sense to switch from using an Excel spreadsheet to an actual database like Access. One possibility is to change the format of your data entry. If you condense the product columns and assign each shopper and date, you can convert it to a pivot table easily.

Here is an example.pivot_grouping_example(solution).xlsx

[ptm0412]

A Pivot table can be created only if the data set is in standard structure. That mean your data must be like this:

Shopper	Date	Product
Jim	31/01/2013	apple
Jim	31/01/2013	Banana
Jim	31/01/2013	Orange

Your real Data is not at that standard, and it may has too many rows and columns to be converted.
So I make a short VBA code to convert Data to standard structure.

Sub ArrangeData()
Dim SArr, RArr
Dim RwC As Long, ColC As Long
With Sheet1.Range("Data")
    SArr = .Value
    RwC = .Rows.Count
    ColC = .Columns.Count
End With
ReDim RArr(1 To RwC * ColC, 1 To 3)
For i = 1 To RwC
    For j = 3 To ColC
        If SArr(i, j) <> "null" Then
            s = s + 1
            RArr(s, 1) = SArr(i, 1)
            RArr(s, 2) = SArr(i, 2)
            RArr(s, 3) = SArr(i, j)
        End If
    Next
Next
With Sheet2
    .Range("A2:c" & .Range("c30000").End(xlUp).Row + 1).ClearContents
    .Range("A2").Resize(s, 3).Value = RArr
    .Range("A1").Resize(s + 1, 3).Name = "Data1"
    .Select
    .PivotTables(1).PivotCache.Refresh
End With

End Sub  

This code can work even if your data includes 10 000 rows and 256 columns. This amount can be adjusted.
Then I make a pivot table to count the product grouping by kind of product.
You can group by date, really.

PS:
you have a "banana " (with an extra space) at the end, so pivot table separate banana into 2 rows.

[AFsimRA]

Thanks everyone. I think the best solution is going to be to reorganize the data. I was hoping not to have to, but ultimately to reduce reporting error it seems this is my best solution.

Thank you for all of your help!