excelmacosif-statementexcel-formulaformula

Is there a way to extract the text from a cell by referencing the cell?

Transaction Data

Example Transaction Data

Header
1.0 X Cold Drinks - Events (Coca Cola)
1.0 X Cold Drinks - Events (Energade)
6.00 X Cold Drinks - Events (Still Water)
1.0 X Cold Drinks - Staff (Still Water)
1.0 X Cold Drinks - Events (Still Water)
1.0 X Cold Drinks - Events (Fanta)
1.0 X Cold Drinks - Events (Cappy)
1.0 X Cold Drinks - Staff (Coca Cola)
1.0 X Cold Drinks - Events (Still Water)
1.0 X Cold Drinks - Events (Coca Cola)

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?

Solution

  • 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

    False Positives

    Concatenation: Cell References vs Text