Forum Discussion
Solved
Excel pivot table help @members
Hi i would wanna ask how do you count the names with the table below? Because i have to show a column where which topic is more people prefer. Like how do i turn counting names to number and sum it?
pan-g I replicated a small part of your table and used Power Query to extract and count the names per Topic.
See if this does what you had in mind. File attached.
Just to show you have a choice, this is an Excel 365 worksheet formula.
= LET( topic, Table1[Topic], data, Table1[[Bus]:[Eng]], NamesByCellλ, LAMBDA(c, COUNTA(TEXTSPLIT(c,CHAR(10)))), Sumλ, LAMBDA(r,SUM(r)), count, IF(data<>"", MAP(data, NamesByCellλ)), countByTopic, BYROW(count, Sumλ), HSTACK(topic, countByTopic) )Do not feel bad if you find that as strange as you find Riny_van_Eekelen 's solution; it is very different from the traditional spreadsheet.
pan-g I replicated a small part of your table and used Power Query to extract and count the names per Topic.
See if this does what you had in mind. File attached.
- How do you do it, could you guide me please.
pan-g If you are new to Power Query, you could start learning by reading through the web-site in the link below. All was done by clicking in the user interface. No complicated manual programming steps were involved. Though, you have to learn which buttons to press and when.
https://exceloffthegrid.com/power-query-introduction/
Once you have connected to the data table, you need to flatten it. In PQ language that's called unpivotting. That transforms a table with separate columns for each department
to a table with only three columns. One for the topic, one for the department and one for the group of names.
Now, understand that the multiple names in one cell are separated by a line-feed in Excel to make them wrap around in a single cell. In PQ you can split the names column into rows based on these line-feeds to turn the previous table into this:
The final step is to Group by topic, counting the rows in each group.
PQ is not difficult to learn, but it takes a while to get into it and you may feel a bit lost in the beginning. Don't let it scare you, though. It's going to be worth the effort.
- J4=COUNTA(E4:I4)
Missed that you have several lines in one range. I think this works:
=COUNTA(TEXTSPLIT(TEXTJOIN(E2,F2,G2,H2,I2),CHAR(10)))
---
Updates:
=COUNTA(TEXTSPLIT(TEXTJOIN(CHAR(10),TRUE,E2,F2,G2,H2,I2),CHAR(10)))
