2 criteria on a COUNTBLANK & SUMPRODUCT nested formula

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
159
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
Joined
Aug 18, 2015
Messages
10,770
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,""))
 

Some videos you may like

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
Joined
Sep 5, 2013
Messages
159
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
Joined
Aug 18, 2015
Messages
10,770
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top