Count If Vlookup Array

[Brett]

I'm trying to come up with a formula that will give me a count if a
vlookup is true for a series of data. Below is a sample data set:

I have a table in C2-D8 as such:

AAA 1-A
ABC 1-A
ACA 1-A
BBA 2-B
BCA 2-B
CBA 3-C
CCC 3-C

I then have this series of data, in F2-F11:
ABC
ACA
ABC
ACA
CBA
BBA
ABC
CCC
AAA

I am looking to get a count for each category in the 2nd column of the
table (ie, a count for 1-A, 2-B, 3-C). Each value in F2-F11 maps to
one of these values. I can do a series of IF statements with
VLOOKUPs, where if it's true, then 1, otherwise 0, and add each
together, but that is not practical for a longer series of data. I
also, don't have the cell space to use a VLOOKUP formula in adjacent
cells and then summing those.

I know for this data set, the results should be:
1-A 6
2-B 1
3-C 3

I'm sure I need some sort of array, but I can't figure it out. I
tried playing with something like this:

{=SUMPRODUCT(IF(VLOOKUP($F$2:$F$11,$C$2:$D$8,2,FALSE)=$E14,1,0))}

But that was not successful.

Any help is greatly apprecaited!

Brett

 

[Gary Keramidas]

maybe it's just me, but your results don't seem to match your dataset. then, you
reference E14 in your sample formula, but give no data for column E.

 

[Lars-Åke Aspelin]

I'm trying to come up with a formula that will give me a count if a
vlookup is true for a series of data. Below is a sample data set:

I have a table in C2-D8 as such:

AAA 1-A
ABC 1-A
ACA 1-A
BBA 2-B
BCA 2-B
CBA 3-C
CCC 3-C

I then have this series of data, in F2-F11:
ABC
ACA
ABC
ACA
CBA
BBA
ABC
CCC
AAA

I am looking to get a count for each category in the 2nd column of the
table (ie, a count for 1-A, 2-B, 3-C). Each value in F2-F11 maps to
one of these values. I can do a series of IF statements with
VLOOKUPs, where if it's true, then 1, otherwise 0, and add each
together, but that is not practical for a longer series of data. I
also, don't have the cell space to use a VLOOKUP formula in adjacent
cells and then summing those.

I know for this data set, the results should be:
1-A 6
2-B 1
3-C 3

I'm sure I need some sort of array, but I can't figure it out. I
tried playing with something like this:

{=SUMPRODUCT(IF(VLOOKUP($F$2:$F$11,$C$2:$D$8,2,FALSE)=$E14,1,0))}

But that was not successful.

Any help is greatly apprecaited!

Brett

I think you have a type for 3-C 3 (should be 3-C 2)

If 1-A, 2-B, and 3-C are in cells E14, E15, and E16, you may try the
following formula in cell F14:

=SUMPRODUCT(($D$2:$D$8=E14)*COUNTIF($F$2:$F$11,$C$2:$C$8))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Copy the formula down to F15 and F16.

Hope this helps / Lars-Åke

 

[Brett]

I think you have a type for 3-C  3 (should be 3-C  2)

If 1-A, 2-B, and 3-C are in cells E14, E15, and E16, you may try the
following formula in cell F14:

=SUMPRODUCT(($D$2:$D$8=E14)*COUNTIF($F$2:$F$11,$C$2:$C$8))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Copy the formula down to F15 and F16.

Hope this helps / Lars- ke- Hide quoted text -

- Show quoted text -

that did it - thakns!!