Another COUNTIF Based On Two Criteria

[bpbenda]

I have a spreadsheet where I am wanting to count the number of occurrences where the text ("isnet") is contained somewhere in Columns A through M, but only where there is no text data in Column J. The relevant range in this spreadsheet is A3:M1740. The formula that I have been trying is:

=COUNTIFS($A3:$M1740,"*isnet*",$J3:$J1740,"="&"")

When I try this formula, I get the "#VALUE!" response. If I break the formula down into its separate COUNTIF parts, I get the numbers that I am looking for. So, not sure what I am doing wrong here. Is it because the first COUNTIF range spans beyond the second criteria? Can someone please help me? Thanks!

 

[Jonmo1]

The criteria ranges in countifs must be the same dimensions.
You can't have one looking at 13 columns, while the other looks at only 1.

Try
=SUMPRODUCT(($J3:$J1740="")*(ISNUMBER(SEARCH("isnet",$A3:$M1740))))

 

[bpbenda]

Brilliant, thanks!

 

[Jonmo1]

You're welcome.