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:

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12[/TD]
[TD]Xx[/TD]
[TD]True[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]14[/TD]
[TD]Xx[/TD]
[TD]False[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]15[/TD]
[TD]Xx[/TD]
[TD]True[/TD]
[TD]True[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11[/TD]
[TD]Xx[/TD]
[TD]False[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]82[/TD]
[TD]Xx[/TD]
[TD]True[/TD]
[TD]False[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]50[/TD]
[TD]Xx[/TD]
[TD]False[/TD]
[TD]False[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]11[/TD]
[/TR]
[TR]
[TD]12[/TD]
[/TR]
[TR]
[TD]82[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.