Count cell values

[mdavid]

Hi, trying to create a table of total occurrences of each cell value.
Here's an example of the csv input file:
Str, N1, N2, N3, N4, N5, N6
Sag, 15, 21, 23, 27, 36, 37
Sag, 8, 11, 13, 18, 25, 31
Sag, 1, 3, 7, 9, 15, 22
Sag, 7, 8, 25, 31, 32, 35
Sag, 3, 5, 8, 18, 19, 29
Sag, 2, 12, 14, 18, 22, 25
Sco, 3, 5, 16, 18, 21, 26
Sco, 6, 16, 23, 24, 30, 33
Sco, 9, 20, 25, 27, 34, 37
Sco, 16, 18, 20, 22, 24, 25
Sco, 8, 9, 13, 14, 27, 29
Sco, 2, 7, 9, 19, 24, 25
Sco, 4, 8, 13, 20, 27, 29
Sco, 6, 7, 11, 17, 22, 29
Sco, 11, 17, 19, 25, 27, 28
Sco, 4, 16, 23, 29, 35, 36
Lib, 7, 20, 21, 25, 35, 37
Lib, 8, 13, 14, 27, 30, 37
Lib, 1, 5, 15, 17, 23, 37
Lib, 6, 16, 21, 27, 28, 29

The 1st column - Str - has 12 possible values, I want 12 columns - 1 for each Str value. The cells in the table contain integers 1-37 in 6 cols - N1 - N6 - the output table should have 12 columns and 37 rows - 1 for each of the 37 integers in the cells, and each cell should contain a count of the integers for that row/column intersect.
So for the above input, output for column:
Sag
1. 1
2. 1
3. 2
4. 0
5. 1
6. 0
7. 2
8. 3
.
.
.
37. 1

Really appreciate any help
Thanks
David

 

[vletm]

mdavid
You should upload an example csv-file and
sample Excel file which has needed result.
... and explain as clear as possible, how from csv should get that output result.

 

[Peter Bartholomew]

You should upload … sample Excel file

That would certainly have made the task easier and reduced the danger of misunderstanding. The first thing to check is that my understanding of your problem is correct.

My preferred strategy would have been to use COUNTIFS but that failed because the number criterion range is 2D whilst the 'Str' field is 1D, a column.

My fall-back solution was to define an array of matches for each number/string combination

Matches:
= (Str=SVal) * (NumRange=NumVal)

Then it only remains to sum the resulting array of values within each cell of the output range.

= SUM( Matches )

If your were to do this without the named formula you would need to commit the formula with CSE cell by cell.

 

[John Jairo V]

Hi, to all!

Another option, using @Peter Bartholomew data, in file.

Formula used : =SUMPRODUCT(($B$3:$B$22=K$2)*($C$3:$H$22=$J3))

Blessings!

 

[mdavid]

Thanks very much Peter for taking the time to do this.
Actually that 1st comment about uploading an example solved my problem, I had no idea how to show an example without a solution, so I managed to do it myself - not as good as your solution, but it worked. Afraid I don't understand where to enter the code for your solution - apart from =Sum(Matches).
Thanks
David

 

[Peter Bartholomew]

@mdavid

Glad you are now sorted. The solution put forward by @John Jairo V is the same functionally as mine but uses SUMPRODUCT as an array formula environment rather than defined names (it also uses standard direct referencing).

Just for future reference, formulas may be given names to describe the values they return by first opening Name Manager, then entering the new name and inserting the formula (starting with '=') into the 'Refers to' box. Whenever the name is referenced, Excel will evaluate the formula it refers to. I use this to break nested formulas into more digestible parts and at the same time ensure that the resulting code is semantically 'readable' rather than merely 'decodable'.