Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count If Vlookup Array
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,FAL SE)=$E14,1,0))} But that was not successful. Any help is greatly apprecaited! Brett |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count If Vlookup Array
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" wrote in message ... 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,FAL SE)=$E14,1,0))} But that was not successful. Any help is greatly apprecaited! Brett |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count If Vlookup Array
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count If Vlookup Array
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
COUNT IF in array | Excel Worksheet Functions | |||
If, Count & Array | Excel Worksheet Functions | |||
COUNT and IF Array | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions |