Tally/count unknown values that appear in several columns

[ocp_zoe]

Hello!

I have six columns of values that I need to tally up by appearance.
Some values appear more than once in a column and sometimes in more than one column.
I've playing round in vlookup tutorial but cannot figure this out and would love to learn.

Example:

Column 1 / Column 2 / Column 3
Apples / Oranges / Bananas
Apples / blank / blank
Apples / bananas / blank
Bananas / blank / blank
Oranges / Apples / blank

How do I total the # of appearances of apples (4), oranges (2) and bananas (3)?

The spreadsheet is massive with many values so I'm looking for a way to do so automatically.

Thank you!

 

[Max1616]

You can use COUNTIF for this.

=COUNTIF(A:C,"Apples") would give you a count of apples

Lets say your data lives in columns A:F and you have what you are trying to tally in cell H2:
=COUNTIF($A:$F,H2)

Hope this helps.

 

[ocp_zoe]

Is there a way to do this without knowing what values are in the columns?
In this example it would be any kind of fruit. I don't want to have to type a COUNTIF for each type of fruit.

Thank you.

 

[Eric W]

You can use the formula in E2 to generate a unique list of values in your columns. Then the COUNTIF can be used to see how many of each one there is.

	A	B	C	D	E	F
1	Column 1	Column 2	Column 3		Unique List	Count
2	Apples	Oranges	Bananas		Apples	4
3	Apples				Oranges	2
4	Apples	Bananas			Bananas	3
5	Bananas		Cherries		Cherries	1
6	Oranges	Apples
7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula F2 =IF(E2="","",COUNTIF($A$2:$C$10,E2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=IF(E1="","",IFERROR(INDIRECT(TEXT(SMALL(IF($A$2:$C$10<>"",IF(COUNTIF($E$1:$E1,$A$2:$C$10)=0,ROW($A$2:$C$10)*100+COLUMN($A$2:$C$10))),1),"R00C00"),0),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Apples	Oranges	Bananas			3​
2​	Apples					Bananas	3​
3​	Apples	bananas				Oranges	2​
4​	Bananas					Apples	4​
5​	Oranges	Apples

In F1 control+shift+enter, not just enter:

=SUM(IF(A1:C5<>"",1/COUNTIFS(A1:C5,A1:C5)))

In F2 control+shift+enter, not just enter, and copy down:

=IF(ROWS(F$2:F2)>$F$1,"",LOOKUP(REPT("z",255),INDEX(IF(COUNTIFS(F$1:F1,A$1:C$5&""),0,A$1:C$5&""),MIN(IF(COUNTIFS(F$1:F1,A$1:C$5&""),"",ROW(A$1:C$5)-ROW(A$1)+1)),0)))

In G2 just enter and copy down:

=IF($F2="","",COUNTIFS(A$1:C$5,$F2))

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Apples	Oranges	Bananas			3​
2​	Apples					Bananas	3​
3​	Apples	bananas				Oranges	2​
4​	Bananas					Apples	4​
5​	Oranges	Apples

In F1 control+shift+enter, not just enter:

=SUM(IF(A1:C5<>"",1/COUNTIFS(A1:C5,A1:C5)))

In F2 control+shift+enter, not just enter, and copy down:

=IF(ROWS(F$2:F2)>$F$1,"",LOOKUP(REPT("z",255),INDEX(IF(COUNTIFS(F$1:F1,A$1:C$5&""),0,A$1:C$5&""),MIN(IF(COUNTIFS(F$1:F1,A$1:C$5&""),"",ROW(A$1:C$5)-ROW(A$1)+1)),0)))

In G2 just enter and copy down:

=IF($F2="","",COUNTIFS(A$1:C$5,$F2))

We need to remove the &"" bit from the formula in F2, so that...

=IF(ROWS(F$2:F2)>$F$1,"",LOOKUP(REPT("z",255),INDEX(IF(COUNTIFS(F$1:F1,A$1:C$5),0,A$1:C$5),MIN(IF(COUNTIFS(F$1:F1,A$1:C$5),"",ROW(A$1:C$5)-ROW(A$1)+1)),0)))

Oyherwise, we probably won't get Cherries Eric puts in the input.