Counting values based off certain values - VLookup?

[ColdplayCoaster]

Hello all,

I've been going around aimlessly with Vlookup to solve this problem. I am trying to count each unique value within a field (B2:B36 in Worksheet "Termd") and add that total to another field in Worksheet "Area 2"). The cell (F2:F14) should only be updated in "Area 2" if it has a value within "Termd B2:B36".

The values for Fields B2:B36 will be one of only 13 choices which will have reptitive values and updated occasionally.

Excel Workbook
	B
1	Location
2	Millennium Force
3	A2 Sweeps
4	Corkscrew
5	Top Thrill Dragster
6	Iron Dragon
7	A2 Sweeps
8	Iron Dragon
9	Iron Dragon
10	Millennium Force
11	Power Tower
12	A2 Sweeps
13	Monster
14	Iron Dragon
15	Corkscrew
Termd

Excel 2007

Thank you in advance.

 

[ColdplayCoaster]

Couldn't find the edit button:

Currently I have this: {=SUM(IF(FREQUENCY(IF(Termd!B$2:B$36=A38,MATCH( Termd!B$2:B$36,Termd!B$2:B$36,0)),ROW(Termd!B$2:B$36)-ROW(Termd!A$38)+1),1))}

Which only gives the result of 1 regardless of how many duplicate values match. Any ideas on what I'm missing here. Thank you.

 

[T. Valko]

Couldn't find the edit button:

Currently I have this: {=SUM(IF(FREQUENCY(IF(Termd!B$2:B$36=A38,MATCH( Termd!B$2:B$36,Termd!B$2:B$36,0)),ROW(Termd!B$2:B$36)-ROW(Termd!A$38)+1),1))}

Which only gives the result of 1 regardless of how many duplicate values match. Any ideas on what I'm missing here. Thank you.

You want the count of unique values, right?

If your range is only a "few" rows then use this formula:

=SUMPRODUCT((Termd!B$2:B$36<>"")/COUNTIF(Termd!B$2:B$36,Termd!B$2:B$36&""))

 

[ColdplayCoaster]

I am not anticipating having more than 100 rows at any time in the worksheet.

EDIT: I don't need the total unique values in the field. I am looking at how many times a certain value occurs within that range.

 

[T. Valko]

I am not anticipating having more than 100 rows at any time in the worksheet.

EDIT: I don't need the total unique values in the field. I am looking at how many times a certain value occurs within that range.

That's how many distinct (aka unique) values are in the posted sample data.

Here's your sample data sorted so it's easier to see what's being counted:

<TABLE style="WIDTH: 184pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=245 border=0 x:str><COLGROUP><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1184" width=37><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 6656" width=208><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 id=td_post_2669902 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 28pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=37 height=17>_1_</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 156pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=208>A2 Sweeps</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A2 Sweeps</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A2 Sweeps</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>_2_</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Corkscrew</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Corkscrew

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>_3_</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Iron Dragon</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Iron Dragon</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Iron Dragon</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Iron Dragon</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>_4_</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Millennium Force</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Millennium Force</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>_5_</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Monster</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>_6_</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Power Tower</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>_7_</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Top Thrill Dragster</TD></TR></TBODY></TABLE>

That is what you want to do, isn't it?

 

[T. Valko]

EDIT: I don't need the total unique values in the field. I am looking at how many times a certain value occurs within that range.

OK, if this is your data:

X
G
X
X
T

You want to know how many Xs are there?

=COUNTIF(A1:A5,"X")

 

[ColdplayCoaster]

Actually, now that I'm looking at the master sheet, that value will come in handy. Ultimately I was banging my head trying to figure out the simplest formula:

=SUM(IF(B2:B36="Corkscrew",1,0))

Time to take a nap! But thank you for the help with the formula that I would have needed in a bit.

 

[T. Valko]

I am not anticipating having more than 100 rows at any time in the worksheet.

In the grand scheme of things, 100 rows is considered a "few"!

 

[T. Valko]

Actually, now that I'm looking at the master sheet, that value will come in handy. Ultimately I was banging my head trying to figure out the simplest formula:

=SUM(IF(B2:B36="Corkscrew",1,0))

Time to take a nap! But thank you for the help with the formula that I would have needed in a bit.

Good deal. Thanks for the feedback!