Ok, say I have the following data in a named range, My_Table, A1:D6:
<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:
<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?
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 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>
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?