SumIF / SumProduct with multiple variables - Help Wanted

[ZeroJoJo]

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!

 

[DonkeyOte]

Perhaps

=SUMPRODUCT(--($D$3:$D$214=$C268),--ISNUMBER(SEARCH(","&D$267&",",","&$I$3:$I$214&",")))
if you wish to be case-sensitive revert to FIND

 

[ZeroJoJo]

Thank you for the quick reply, I am going to plug it and and see if it works.

It seems good, albeit gives me weird numbers, will have to check some of them by hand.

BTW the search doesn't need to discriminate on caps or lower case.


I am unsure if it matters, but there is a space between the comma and the next name.

 

[DonkeyOte]

I am unsure if it matters, but there is a space between the comma and the next name.

Yes, it will impact the formula, in which case try

=SUMPRODUCT(--($D$3:$D$214=$C268),--ISNUMBER(SEARCH(", "&D$267&",",", "&$I$3:$I$214&",")))

 

[ZeroJoJo]

I think you nailed it! I now have results which match the data! Thank you SO MUCH!!

One day maybe I'll actually understand what all that in the formula means :D for now I wish the CountIF function could accept more than just 1 parameter.