Horizontal COUNTIF?

[RaviWildcat]

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

 

[njimack]

Excel Workbook
	A	B	C	D
1	Name	Language 1	Language 2	Language 3
2	Greg	English	Spanish	French
3	Peter	English	Spanish
4	Bobby	English
5	Marcia	Spanish	English	Italian
6	Jan	English	French
7	Cindy	English	French
8
9	Name	Languages Spoken
10	Bobby	1
11	Cindy	2
12	Greg	3
13	Jan	2
14	Marcia	3
15	Peter	2
Sheet1

 

[circledchicken]

Hi,

Another alternative:

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Language 1	Language 2	Language 3		Name	Languages Spoken
2	Greg	English	Spanish	French		Bobby	1
3	Peter	English	Spanish			Cindy	2
4	Bobby	English				Greg	3
5	Marcia	Spanish	English	Italian		Jan	2
6	Jan	English	French			Marcia	3
7	Cindy	English	French			Peter	2
Sheet1

Excel 2010

Cell Formulas
Range	Formula
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.

 

[RaviWildcat]

Both solutions are awesome!

Thank you so much!

Ravi

 

[circledchicken]

Your welcome - thanks for the feedback.

 

[willow1985]

Hi,

Another alternative:

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Language 1	Language 2	Language 3		Name	Languages Spoken
2	Greg	English	Spanish	French		Bobby	1
3	Peter	English	Spanish			Cindy	2
4	Bobby	English				Greg	3
5	Marcia	Spanish	English	Italian		Jan	2
6	Jan	English	French			Marcia	3
7	Cindy	English	French			Peter	2
Sheet1

Excel 2010

Cell Formulas
Range	Formula
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.

How would you modify this if you needed another match? for Example if instead of 1 column: languages spoken you had 3 columns English, Spanish & French. I know that this would return 1 for each but the data I have would require the additional match. Any help would be greatly appreciated!