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

Question:

# Question:Q: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

Posted on

Question marked as Solved

You can try something like this:

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:

=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

Question marked as Solved

You can try something like this:

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:

=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

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. ;-)