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

[kcmuppet]

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:

=countifs(
Data_Table[[#Headers],[Q1This]:[Q58Task]],"*Task",
Data_Table[[Q1This]:[Q58Task]],"*texttofind*")

...but I get a value error

Anybody know how to do this?

 

[duggie33]

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

 

[Fluff]

How about

=SUMPRODUCT((RIGHT(Data_Table[[#Headers],[Q1This]:[Q58Task]],4)="Task")*(ISNUMBER(SEARCH("texttofind",Data_Table[[Q1This]:[Q58Task]]))))

 

[kcmuppet]

...The TOCOL arranges the first array into a single column, filters for only zeroes, and counts the result.
...

Doug

Thanks - sadly I don't have a TOCOL function yet

 

[duggie33]

I would put money on Fluff's solution

 

[kcmuppet]

I would put money on Fluff's solution

I'm going to try it when I get home!

 

[kcmuppet]

How about

=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?

 

[Fluff]

What's wrong with using sumproduct?

 

[kcmuppet]

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 .

 

[Fluff]

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.