Count occurrences of value in table if column headers contains specific text

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
435
Office Version
  1. 365
  2. 2010
I'm trying to count occurrences of a partial match text string within cells in table columns, where the column header contains specific text.

For example, for all the columns in my table 'Data_Table' between column [Q1This] and column [Q958Task] where the header contains ends in the text "Task", and the cell value is zero, I thought this would do it:
Excel Formula:
=countifs(
Data_Table[[#Headers],[Q1This]:[Q58Task]],"*Task",
Data_Table[[Q1This]:[Q58Task]],"*texttofind*")

...but I get a value error

Anybody know how to do this?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
This formula worked on my quickly thrown together Table1 full of random numbers and a handful of zeroes. The basics of the formula; first it filters only columns that contain the string "task". Critical to note that it will find all that contain "task", not only those that end in "task". It also checks all headers, rather than some of the headers. The TOCOL arranges the first array into a single column, filters for only zeroes, and counts the result.

=LET(arr_1,FILTER(Table1,ISNUMBER(SEARCH("task",Table1[#Headers],1))),arr_2,TOCOL(arr_1,0,TRUE),COUNT(FILTER(arr_2,arr_2=0)))

I suggest you test it on a small set of data to make sure it works as you expect it to. Maybe check it with blanks, text, etc. Hopefully this gets you a step closer to your solution.

Doug
 
Upvote 0
How about
Excel Formula:
=SUMPRODUCT((RIGHT(Data_Table[[#Headers],[Q1This]:[Q58Task]],4)="Task")*(ISNUMBER(SEARCH("texttofind",Data_Table[[Q1This]:[Q58Task]]))))
 
Upvote 0
Solution
How about
Excel Formula:
=SUMPRODUCT((RIGHT(Data_Table[[#Headers],[Q1This]:[Q58Task]],4)="Task")*(ISNUMBER(SEARCH("texttofind",Data_Table[[Q1This]:[Q58Task]]))))
Thanks @Fluff . This works. Is there an efficient way to do this without using sumproduct?
 
Upvote 0
What's wrong with using sumproduct?
 
Upvote 0
I love sumproduct, but with a lot of them and a large table it slows down and I have a lot of blue spinning circle time. I'm wondering if there's a way to use countifs to achieve the same result but work more efficiently .
 
Upvote 0
You cannot do that with countifs as the ranges are not the same sie.
For 2010 I suspect that sumproduct is your only option, but there maybe other options for 365 if you have the TOCOL function.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top