Count numbers where the heading partially matches

[prasaddn]

Hi All,

I used to write formulas for such requirements and it is being quite some time and l lost touch. I remember using array formulas - sumproduct, mmult etc but not able to get the right combination now.

I need formula for the below scenario.
count no of columns where we have numbers and heading has "Marketing New Change". Sample attached.

	first Marketing New Change​	First Internal Change​	Second Marketing New Changes​	random Column/s​	forth Marketing New Changes​	forth Internal Changes​	fifth Marketing New Changes​	Need Formula​	Actual Required Answer​
Scenario 1	2​		2​			2​	1​		3​
Scenario 2		2​	2​	05-08-2021​	4​	3​	2​		3​
Scenario 3	2​	2​		05-08-2021​		3​	2​		2​
Scenario 4			2​	05-08-2021​		3​			1​
Scenario 5		4​	2​	05-08-2021​	4​	5​			2​

Kindly help.
Regards,
Prasad DN

 

[bosco_yip]

Try,

In I2, formula copied down :

=COUNTIFS(A$1:H$1,"*Marketing New Change*",A2:H2,"<>")

 

[prasaddn]

LOL :D yes, I just realized it as well.

thank you.

Regards,
Prasad DN

 

[Peter Bartholomew]

If you want to use array formulae,

= LET(
  filter, --TRANSPOSE(ISNUMBER(FIND("Marketing New Change", Heading))),
  array,  --ISNUMBER(data),
  MMULT(array, filter) )

would give the same result as a dynamic array. Finding the string in the header is not especially elegant though.