Count items in range based on range of criteria

[dmatter26]

Ok, say I have the following data in a named range, My_Table, A1:D6:

	A	B	C	D
1	12	Xx	True	True
2	14	Xx	False	True
3	15	Xx	True	True
4	11	Xx	False	False
5	82	Xx	True	False
6	50	Xx	False	False

<tbody>
</tbody>

Column A is a list of ID#'s.
​

Column B is names which I don't care about in this example, just included to show spacing between A and C.
Column C, D are sets of True/False criteria.

What I want is, ideally, a formula that would take a set of input ID's, look at the table and give me a count of those ID's with a corresponding True in a variable column (C, D, E, etc.)

So if I have a five row range (preferably non-named, eg. G1:G5) with three values in it:

11

12

82

<tbody>
</tbody>

and am looking for True in Column C, I would have a formula in H1 that gave me a result of 2. Those 3 ID's all appear in column A (that's already a guarantee), but only two of them are flagged as True in Column C. If I was looking at Column D, the result would be 1, since only 1 of them is flagged as true in Column D.

I can do this for a single value in G1:G5, looking at Column C, with

=COUNTIFS(INDEX(My_Table,,1),G1,INDEX(My_Table,,3),TRUE)

but I can't figure out how to scale this to check the entire G1:G5 range with a formula. I know I can build a VBA function to do this, but I just figure there has to be a more... elegant way of doing this with Excels built-in formulas. Help?

 

[Marcelo Branco]

To count TRUE in column C for all IDs in G1:G5 try

=SUMPRODUCT(COUNTIFS(INDEX(MyTable,,1),G1:G5,INDEX(MyTable,,3),TRUE))

M.

 

[dmatter26]

*sigh* I knew it was simple - that worked like a charm. Thanks so much!

 

[Marcelo Branco]

*sigh* I knew it was simple - that worked like a charm. Thanks so much!

You are welcome and thanks for the feedback.

M.