Transaction Data

Example Transaction Data

Transaction Data

Example Transaction Data

I have this data extracted from our POS system website.
I want to put it into a matrix to count how many of each item we sold and the amount we sold. You will see some items say 3x Still Water or 1x Still Water. So I want to have a matrix that (as an example) says we sold 7 of 3x Still Water and 20 of 1x Still Water.

Matrix Example

This is the matrix I set up and I want to use cell references instead of going to change the criteria for every cell that the formula is in so I can just drag out the formula to the right and down.
But whenever I want to use the cell as a reference with wildcards it the uses the cell name as text (A1) instead of what is contained in the cell (Still Water).

If I type the formula as;

=COUNTIFS(Transactions!$A$1:$A$904,"*Still Water*",Transactions!$A$1:$A$904,"*1.0*") 

it works and returns 227 as the amount of single still waters sold,
However, if I change it to;

=COUNTIFS(Transactions!$A$1:$A$904,"*A5*",Transactions!$A$1:$A$904,"*B4*") 

it gives me 0, because it is using A5 and B4 the actual text as reference and not what is contained in cell A5 and B4.

I would like to use the second formula, because then I can just drag it down or to the right and it will automatically change criteria based on what is in the cell, instead of me having to go and type out the criteria everytime for each different cell.

I hope this makes sense?

• excel
• macos
• if-statement
• excel-formula
• formula

3 Answers 3

Use Wild Characters and Avoid False Positives When Counting

In cell B5:

=COUNTIFS(Transactions!$A$1:$A$904,"*("&$A5&")",Transactions!$A$1:$A$904,B$4&"*")

Locking Rows and Columns

• To be able to copy the formula across, you need to lock the row and column appropriately:
  • Lock column A with $A.
  • Lock row 4 with $4.

False Positives

• To avoid false positives, you need to add the parentheses at the end to the formula.
• If you don't, e.g.,
  • Coca Cola Zero will also be added to the count of Coca Cola,
  • Light Fanta will also be added to the count of Fanta.

Concatenation: Cell References vs Text

• In a formula, e.g.,
  • A5 is a cell reference,
  • "A5" is text.
• You need to concatenate cell references, wild characters or any other text using the concatenation character &:
  • "*("&$A5&")" - ends with
  • B$4&"*" - begins with

A single formula that will create the entire matrix. The included SEQUENCE function determines how many columns. You can change the formatting of the column headers to whatever you want within the formula.

• The data is in a Table with the name Drinks

Data

=LET(
    d, Drinks,
    x, REDUCE(
        SEQUENCE(12),
        Drinks[Header],
        LAMBDA(a, b,
            VSTACK(a, REGEXEXTRACT(b, "^(\d+(?:\.\d+)\b)[^(]*\(([^)]*)", 2))
        )
    ),
    m, MAP(x, LAMBDA(p, IF(ISNUMBER(-p), --p, p))),
    p, PIVOTBY(
        CHOOSECOLS(m, 2),
        CHOOSECOLS(m, 1),
        CHOOSECOLS(m, 2),
        COUNTA,
        0,
        0,
        ,
        0
    ),
    f, FILTER(p, NOT(ISNA(CHOOSECOLS(p, 1)))),
    f
)

Matrix Results

You can do all by a single formula. Try-

=LET(x,TEXTBEFORE(A1:A10," "), y,TEXTBEFORE(TEXTAFTER(A1:A10,"("),")"), PIVOTBY(y,x,y,COUNTA,0,0,,0))

Input Data:

Output by the formula: