About Us

Brett[_3_]

On 6 Jan, 17:17, Lars- ke Aspelin wrote:
On Wed, 6 Jan 2010 13:50:01 -0800 (PST), Brett

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,FA LSE)=$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!!

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Complex conditional summing - array COUNT works, array SUM gives#VALUE	fatcatfan	Excel Worksheet Functions	4	November 18th 09 06:41 PM
COUNT IF in array	WildWill	Excel Worksheet Functions	2	October 16th 08 10:18 AM
If, Count & Array	Bec[_3_]	Excel Worksheet Functions	2	April 23rd 08 05:45 AM
COUNT and IF Array	bob	Excel Worksheet Functions	3	December 2nd 07 03:08 PM
Vlookup in vlookup - taking the result as array name	SupperDuck	Excel Worksheet Functions	2	June 2nd 07 11:05 AM

Menu

About Us