Horizontal COUNTIF?

RaviWildcat

Board Regular
Joined
Jun 18, 2010
Messages
97
Hello everyone,

I've got 2 tables

Table 1, containing someone's first, second and third language

<TABLE style="WIDTH: 204pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=273><COLGROUP><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=3 width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: #4f81bd; WIDTH: 36pt; HEIGHT: 15pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=48>Name</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 56pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl66 width=75>Language 1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 56pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl66 width=75>Language 2</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 56pt; BORDER-TOP: #95b3d7 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl66 width=75>Language 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>Greg</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68>English</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68>Spanish</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68>French</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl69 height=20>Peter</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70>English</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70>Spanish</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>Bobby</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68>English</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68></TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl69 height=20>Marcia</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70>Spanish</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70>English</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70>Italian</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: #dce6f1; HEIGHT: 15pt; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>Jan</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68>English</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68>French</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl68></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl69 height=20>Cindy</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70>English</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70>French</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #95b3d7; BORDER-RIGHT: #f0f0f0" class=xl70></TD></TR></TBODY></TABLE>

and I'm trying to populate a table listing the number of languages spoken by each person (I'm listing the names alphabetically in table 2)

<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=202><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #4bacc6 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl66 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #4bacc6 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 104pt; FONT-FAMILY: Calibri; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl66 width=138>Languages Spoken</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #daeef3; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4bacc6 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DAEEF3 none" class=xl65 height=20>Bobby</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #4bacc6 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #daeef3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DAEEF3 none" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4bacc6 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>Cindy</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #4bacc6 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #daeef3; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4bacc6 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DAEEF3 none" class=xl65 height=20>Greg</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #4bacc6 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #daeef3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DAEEF3 none" class=xl65 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4bacc6 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 height=20>Jan</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #4bacc6 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl65 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #daeef3; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4bacc6 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DAEEF3 none" class=xl65 height=20>Marcia</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #4bacc6 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #daeef3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DAEEF3 none" class=xl65 align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #4bacc6 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67 height=20>Peter</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #4bacc6 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" class=xl67 align=right>2</TD></TR></TBODY></TABLE>

I'm not sure what formula to use for 'Languages Spoken'.

(I don't want to use a helper column or a pivot table)

The logic would be - For each name in languages spoken, count the nonblank entries in language1, language2, and language 3, then place the number in the appropriate column in languages spoken.

(I can create a helper column in table 1 - the formula would be =counta(language1:language3) and then use an index(match) to match the helper column in table 1 based on the name, but that's cheating.

I can also create a pivot table in a few seconds but that's no fun.)

My instinct tells me to use countif except that I'm counting items horizontally instead of vertically - is there a horizontal countif?

Ravi
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Excel Workbook
ABCD
1NameLanguage 1Language 2Language 3
2GregEnglishSpanishFrench
3PeterEnglishSpanish
4BobbyEnglish
5MarciaSpanishEnglishItalian
6JanEnglishFrench
7CindyEnglishFrench
8
9NameLanguages Spoken
10Bobby1
11Cindy2
12Greg3
13Jan2
14Marcia3
15Peter2
Sheet1
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi,

Another alternative:
Excel Workbook
ABCDEFG
1NameLanguage 1Language 2Language 3NameLanguages Spoken
2GregEnglishSpanishFrenchBobby1
3PeterEnglishSpanishCindy2
4BobbyEnglishGreg3
5MarciaSpanishEnglishItalianJan2
6JanEnglishFrenchMarcia3
7CindyEnglishFrenchPeter2
Sheet1
Excel 2010
Cell Formulas
RangeFormula
G2=COUNTA(INDEX(B$2:D$7,MATCH(F2,A$2:A$7,0),0))
The COUNT functions should work with any range I think, horizontal or vertical.
 

Forum statistics

Threads
1,136,778
Messages
5,677,679
Members
419,712
Latest member
LearningCR

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