Counting values based off certain values - VLookup?

ColdplayCoaster

New Member
Joined
Mar 5, 2011
Messages
29
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
1Location
2Millennium Force
3A2 Sweeps
4Corkscrew
5Top Thrill Dragster
6Iron Dragon
7A2 Sweeps
8Iron Dragon
9Iron Dragon
10Millennium Force
11Power Tower
12A2 Sweeps
13Monster
14Iron Dragon
15Corkscrew
Termd
Excel 2007

Thank you in advance.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

ColdplayCoaster

New Member
Joined
Mar 5, 2011
Messages
29
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Mar 5, 2011
Messages
29
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.
 
Last edited:

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Mar 5, 2011
Messages
29

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top