Forum Discussion
Counting instances across three columns not just one
Hi, I am trying to figure out how to code for the resulting tables #1 and #2 in the screenshot, based on the data table. Each publication can be characterized by 1 to 3 categories of humour in domain A and 1-3 categories of group in domain B. I manually created the tables I want to achieve (#1 and#2 in the screenshot) to demonstrate what I want to do. I can make a pivot table with publication type and one column of the humour categories, but I can't figure out how to make a pivot table or use a function like COUNTIFS to do it across 3 columns. I could brute force a solution by copy/pasting all the responses into one big linear spreadsheet but there must be a more elegant way. (These are made up data to demonstrate what I am after). Thank you.
You can count your entries with a combination of FILTER() and SUMPRODUCT().
=SUMPRODUCT(--(TOCOL(FILTER(Publication[[Acategory1]:[Acategory3]],Publication[Type]=B$16),1)=$A17))COUNTIF() does not seem to work with a filtered list.
4 Replies
=SUMPRODUCT(N(IF(($E$2:$G$10=C$14),($B$2:$B$10=$B15)+($C$2:$C$10=$B15)+($D$2:$D$10=$B15))))
The suggestion in the attached file uses SUMPRODUCT. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
You can count your entries with a combination of FILTER() and SUMPRODUCT().
=SUMPRODUCT(--(TOCOL(FILTER(Publication[[Acategory1]:[Acategory3]],Publication[Type]=B$16),1)=$A17))COUNTIF() does not seem to work with a filtered list.
- Thank you! What is the double hyphen in front to (TOCOL ?
This turns true or false into a 1 or a 0, from which the sum can be calculated.
