Count If Vlookup Array

  • Thread starter Thread starter Brett
  • Start date Start date
B

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
 
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.
 
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
Click to expand...

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
 
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 -
Click to expand...

that did it - thakns!!
 
Back
Top