I am having trouble with an excel formula

Question

I am trying to come up with a formula in excel that allows me to have two separate criteria from two separate lists and then sum the total amount of values that meet those criteria. I can get the set of criteria to work, so =COUNTIFS(Data!A:A,[@[Sub ID]] section allows me to see the amount of times a specific supID appears in column A. However, the second part where I am trying to see after that how many times any value from a particular list of values appears in a separate list, does not work.

Is there any way to make a formula count one list of values against another list of values without having to name each additional value in the formula.

I could do this if I wrote,

=SUM(COUNTIFS(Data!A:A,[@[Sub ID]],Data!B:B,{"=apples","=pears","=bananas"[....]}))

But I don't want to have to write out each additional value, the list is too long. I just want to have the list in a column so I can reference it in the equation but it wont let me.

=SUM(COUNTIFS(Data!A:A,[@[Sub ID]],Data!B:B,Mapping2!B:B))

Answer 1

You are creating an iterative formula, You want to limit the last criterion to just the data set:

Mapping2!B2:B15

Then use SUMPRODUCT which will force the iteration:

=SUMPRODUCT(COUNTIFS(Data!A:A,[@[Sub ID]],Data!B:B,Mapping2!B2:B15))