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
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
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
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
Thanks SGIII. Worked a treat. I would never have worked that out. Cheers 👍-1F3FD;😁
Counting the number of times a specific word appears in any of 4 columns with a checked box on the same row.
