# 2 criteria on a COUNTBLANK & SUMPRODUCT nested formula

#### drewberts

##### Board Regular
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

##### MrExcel MVP
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,""))

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### drewberts

##### Board Regular
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

##### MrExcel MVP
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!

Replies
5
Views
61
Replies
6
Views
467
Replies
9
Views
243
Replies
2
Views
155
Replies
7
Views
413

1,127,867
Messages
5,627,354
Members
416,244
Latest member
JBKool

### 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.

### Which adblocker are you using?

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

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