Hello all,
I have a problem trying to count the number a certain event happens. I need to count how many times variable A is X at the same time as B is Y. The problem lies that Y is a comma separated list of items (for example first names).
I use the following formula:
=SUMPRODUCT(($D$3:$D$214=$C268)*($I$3:$I$214=D$267))
The items searched are always on the same row.
The problem lies in this ::: $I$3:$I$214=D$267 ::: part, as when I ask it to look for the items present in cell D267, I cannot tell it to skip any unwanted info.
I would normally do that my adding "*"& to the cell number, such as this:
$I$3:$I$214="*"&D$267&"*"
Unfortunately this returns a 0 count. I know that the "*"& works with a normal CountIf, why does it not work here?
Naturally if I leave the formula as it is, it will only count the times the exact contents of cell D267 are found, but this is not what I need.
To recap I have a situation such as:
Column A has values, 1 per cell, but column B has lists of names, more than one per cell, separated by a comma.
Thank you in advance for the help!
I have a problem trying to count the number a certain event happens. I need to count how many times variable A is X at the same time as B is Y. The problem lies that Y is a comma separated list of items (for example first names).
I use the following formula:
=SUMPRODUCT(($D$3:$D$214=$C268)*($I$3:$I$214=D$267))
The items searched are always on the same row.
The problem lies in this ::: $I$3:$I$214=D$267 ::: part, as when I ask it to look for the items present in cell D267, I cannot tell it to skip any unwanted info.
I would normally do that my adding "*"& to the cell number, such as this:
$I$3:$I$214="*"&D$267&"*"
Unfortunately this returns a 0 count. I know that the "*"& works with a normal CountIf, why does it not work here?
Naturally if I leave the formula as it is, it will only count the times the exact contents of cell D267 are found, but this is not what I need.
To recap I have a situation such as:
Column A has values, 1 per cell, but column B has lists of names, more than one per cell, separated by a comma.
Thank you in advance for the help!
