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.

--


Gary Keramidas
Excel 2003


"Brett" <(E-Mail Removed)> wrote in message
news:28c62e8b-4d85-432b-aa37-(E-Mail Removed)...
> 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

On Wed, 6 Jan 2010 13:50:01 -0800 (PST), Brett
<(E-Mail Removed)> wrote:

>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

On 6 Jan, 17:17, Lars- ke Aspelin <lar...@REMOOOVE.telia.com> wrote:
> On Wed, 6 Jan 2010 13:50:01 -0800 (PST), Brett
>
>
>
>
>
> <brett.kap...@gmail.com> wrote:
> >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- Hide quoted text -
>
> - Show quoted text -

that did it - thakns!!