2 criteria on a COUNTBLANK & SUMPRODUCT nested formula

[drewberts]

I have a table which tracks hundreds of products and I'm looking to summarise the number of blanks in each column. However (for various reasons) there are several blank lines at the end of table which I want to exclude from the count. I've setup a formula which counts the number of blanks in a column (column C in this case) based on only adding to the count if there is something in column A (This bit I can get to work fine):-

=COUNTBLANK(C6:INDEX(C:C,SUMPRODUCT(MAX(($A:$A<>"")*ROW(C:C)))))

I've then added in another criteria (which is where my problem is) and I've used an AND function to also not add to the count if there is any text string that includes the letters "test" in column B. I don't get an error with my syntax but this returns the wrong calculated value:-

=COUNTBLANK(C6:INDEX(C:C,AND(SUMPRODUCT(MAX(($A:$A<>""),SUMPRODUCT(($B:$B<>"Test*")*ROW(C:C)))))))

Can anyone see what my problem is?

Thanks

 

[Eric W]

I can adapt that formula to include an OR equivalent. You really can't use OR by itself in array formulas. But before I try that, let me ask this. You're looking for the last non-empty cell in column D. It appears that column C will be populated in either case. Can we look for the last populated cell in C instead? Or will there be a case where there's a date in D and nothing in C? If we can restate our problem as "count all the rows where C is populated (except with TENDER or Banana), and D is empty", then we can try this simpler formula:

=COUNTIFS(C6:C10000,"*",D6:D10000,"")-SUM(COUNTIFS(C6:C10000,{"*tender*","*banana*"},D6:D10000,""))

 

[drewberts]

Now you've mentioned it...

Yep, Col C will always have something in it. I've run with your formula above and not only is it something I understand, but its a **** sight simpler!

Thanks again Eric. Stay safe & healthy.

 

[Eric W]

Whew! Glad that works! Simpler is always better, but sometimes you just have to look at the problem from a different angle. Anyway, good luck and stay safe!