Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Counting the number of times a specific word appears in any of 4 columns with a checked box on the same row.

I want to count the number of successful procedures. The procedures appear in any of columns I-L, and success is indicated by a checked box in column Q.

I can make it work for a single ‘block’ column (e.g. I or K or J) using COUNTIFS, but this does not work if I expend the reference values to include more than 1 column.


Any help or suggestions appreciated

User uploaded file

Posted on

Reply
Question marked as Solved
Answer:
Answer:

You can try something like this:


User uploaded file


Add an "index" column (which you can later hide) with a formula like this:


=IF(Q2,I2&J2&K2&L2,"")


Then derive the counts with a formula like this:


User uploaded file


=COUNTIF(Table 1::R,"*"&A2&"*")


Those * are wildcard operators that allow COUNTIF to look for strings in the index column that "contain" the value in column A of that row.


SG

Posted on

Page content loaded

Question marked as Solved

Nov 15, 2017 6:44 AM in response to Beastjake In response to Beastjake

You can try something like this:


User uploaded file


Add an "index" column (which you can later hide) with a formula like this:


=IF(Q2,I2&J2&K2&L2,"")


Then derive the counts with a formula like this:


User uploaded file


=COUNTIF(Table 1::R,"*"&A2&"*")


Those * are wildcard operators that allow COUNTIF to look for strings in the index column that "contain" the value in column A of that row.


SG

Nov 15, 2017 6:44 AM

Reply Helpful

Nov 15, 2017 7:25 PM in response to SGIII In response to SGIII

Nice one!


At first glance, on the way out this morning, I didn't notice the reference to the checkbox, and was scratching my head a bit. Fortunately,I decided there wasn't time to reply. ;-)


Re-read this evening, and had the Aha!


Regards,

Barry

Nov 15, 2017 7:25 PM

Reply Helpful
User profile for user: Beastjake

Question: Counting the number of times a specific word appears in any of 4 columns with a checked box on the same row.