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.