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
Column 1	Column 2	Column 3	Unique List	Count
Apples	Oranges	Bananas	Apples
Apples	Oranges
Apples	Bananas	Bananas
Bananas	Cherries	Cherries
Oranges	Apples

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(E2="","",COUNTIF($A$2:$C$10,E2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=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),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]Apples[/td][td]Oranges[/td][td]Bananas[/td][td][/td][td][/td][td]

3​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]Apples[/td][td][/td][td][/td][td][/td][td][/td][td]Bananas[/td][td]

3​

[/td][/tr]
[tr][td]

3​

[/td][td]Apples[/td][td]bananas[/td][td][/td][td][/td][td][/td][td]Oranges[/td][td]

2​

[/td][/tr]
[tr][td]

4​

[/td][td]Bananas[/td][td][/td][td][/td][td][/td][td][/td][td]Apples[/td][td]

4​

[/td][/tr]
[tr][td]

5​

[/td][td]Oranges[/td][td]Apples[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]Apples[/td][td]Oranges[/td][td]Bananas[/td][td][/td][td][/td][td]

3​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]Apples[/td][td][/td][td][/td][td][/td][td][/td][td]Bananas[/td][td]

3​

[/td][/tr]
[tr][td]

3​

[/td][td]Apples[/td][td]bananas[/td][td][/td][td][/td][td][/td][td]Oranges[/td][td]

2​

[/td][/tr]
[tr][td]

4​

[/td][td]Bananas[/td][td][/td][td][/td][td][/td][td][/td][td]Apples[/td][td]

4​

[/td][/tr]
[tr][td]

5​

[/td][td]Oranges[/td][td]Apples[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.