Complex pivot-like summary without VBA

Question

Given a set of data like this:

User    Item1 Desc.    Item1 Sel.    Item2 Desc.    Item2 Sel.    Item3 Desc.    Item3 Sel.
UserA   Item 1         Yes           Item 2         Yes           Item 3         Yes
UserB   Item 1         Yes           Item 2         No            Item 3         No
UserB   Item 1         Yes           Item 2         Yes           Item 3         No
UserC   Item 1         Yes           Item 2         No            Item 3         Yes
UserA   Item 1         No            Item 2         Yes           Item 3         Yes
UserA   Item 1         Yes           Item 2         No            Item 3         Yes

Is there a way I can use built-in Excel tools, functions, or formulas to produce an output like this

Item    UserA    UserB    UserC
Item1   1        0        0
Item2   1        1        1
Item3   0        2        0

Where both Items and Users are consolidated, and the value presented is the count of Nos within the table?

I was asked to prepare this for one of my colleagues, and ended up creating the VBA function below. It's quick and dirty, and will need to be refactored, for sure, but it did get me what I needed.

Sub SummarizeData()

Dim wksSrc As Worksheet
Dim dicAssoc As Object
Dim l As Long
Dim i As Integer
Dim rngCell As Range
Dim v As Variant

    Set dicAssoc = CreateObject("Scripting.Dictionary")
    With ThisWorkbook.Sheets.Add()
        .Name = "Results"
        Set wksSrc = ThisWorkbook.Sheets("data")
        For l = 2 To 184
            If Not dicAssoc.exists(wksSrc.Range("C" & l).Value) Then
                dicAssoc.Add wksSrc.Range("C" & l).Value, wksSrc.Range("C" & l).Value
            End If
        Next l
        .Range("A1:A26").Value = ThisWorkbook.Sheets("Sheet5").Range("A1:A26").Value
        l = 2
        For Each v In dicAssoc.items
            .Cells(1, l).Value = v
            l = l + 1
        Next v
        For Each rngCell In wksSrc.Range("A2:CT184").Cells
            For l = 2 To 26
                If rngCell.Value = .Cells(l, 1).Value Then
                    If rngCell.Offset(0, 1).Value = "No" Then
                        For i = 2 To 148
                            If .Cells(1, i).Value = wksSrc.Range("C" & rngCell.Row).Value Then
                                .Cells(l, i).Value = .Cells(l, i).Value + 1
                            End If
                        Next i
                    End If
                End If
            Next l
        Next rngCell
    End With
End Sub

I originally tried to tackle this problem without code, but after a little trial and error, decided it best not to waste my whole day on this project. I thought I could try DCOUNTA, but that required me to pull multiple extra lines of data that would have been a waste of space:

DCOUNT criteria example:

Item1 Sel.    Item2 Sel.    Item3 Sel.    User
No                                        UserA
              No                          UserA
                            No            UserA
No                                        UserB
              No                          UserB
                            No            UserB

And so on and so forth. With about 25 items and about 25 users, this was going to be a huge block, most of which would have to be manually created. At that point, I might as well have taken to counting everything by hand anyways.

I also thought about using some combinations of VLOOKUP, HLOOKUP, INDEX, and INDIRECT, but I could never get a result that counted ALL the Nos; it always just caught the first line it found.

I'm wondering if there is a relatively simple way to generate this summary without using VBA.

Answer 1

What you want can be easily achieved using SUMPRODUCT Formula.

Please see the attached sample and screenshot.

To find the number for "No" for Item 1 and For User 1, the formula I have used is

=SUMPRODUCT(($A$2:$A$7=$B$11)*($C$2:$C$7="No"))

Similarly you have to do it for the rest.

Sample file:

http://wikisend.com/download/725440/Gaffi.xlsx

Snapshot: