2 criteria on a COUNTBLANK & SUMPRODUCT nested formula

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
161
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
The problem is the AND function. When you are creating an array formula, with a 2-D array the AND will AND every element in the array together, when you are expecting it to just AND the two results on a given row. To get around that, you can use the * operator instead of AND, like:

=COUNTBLANK(C6:INDEX(C:C,MAX((A6:A1000<>"")*ISERROR(SEARCH("test",B6:B1000))*ROW(B6:B1000))))

confirmed with Control+Shift+Enter (if you don't have Excel 365). You could also use this regularly entered formula:

=COUNTBLANK(C6:INDEX(C:C,AGGREGATE(14,6,ROW(C6:C1000)/(A6:A1000<>"")/ISERROR(SEARCH("test",B6:B1000)),1)))

Notice in both cases I used an actual maximum row instead of an entire column reference for performance reasons.
 

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
161
Thanks @Eric W I've run with the second of your suggestions. Its works in as much as counting the correct number of blanks but isn't ignoring the ones where TEST appears in 2nd column to check. To clarify the TEST characters can be anything that includes TEST, e.g. TESTING, TESTER etc (so I've used wildcards in my example below. This is what I'm now using (adapted to my sheet) which isn't quite working:-

=COUNTBLANK(Tenders!D6:INDEX(Tenders!D:D,AGGREGATE(14,6,ROW(Tenders!$C$6:$C$10000)/(Tenders!$A$6:$A$10000<>"")/ISERROR(SEARCH("*TEST*",Tenders!$B$6:$B$10000)),1)))

Tenders is the name of the sheet on which I'm checking
Col A is the first column to check if there are any blanks
Col C is the column to check if it contains any derivitive of TEST (note the use of *wildcards which may be the problem)
Col D is the column which contains the blanks I'm trying to count
I've used $ where I want to fix the cell refs as I want to copy the formula along a row

It all looks fine to me and does return the correct number of blanks in the colum (excluding the rowsa that have a blank in column A. It just wont exclude those rows which have TEST in column B
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
When using SEARCH, you don't need the * characters to denote wildcards. SEARCH will return the position within the cell where the search string starts. So unless you actually have "*TEST*" in the cell, it won't match. Also note that SEARCH is case-insensitive, while FIND is case sensitive if that makes a difference.

=COUNTBLANK(Tenders!D6:INDEX(Tenders!D:D,AGGREGATE(14,6,ROW(Tenders!$C$6:$C$10000)/(Tenders!$A$6:$A$10000<>"")/ISERROR(SEARCH("TEST",Tenders!$B$6:$B$10000)),1)))
 

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
161

ADVERTISEMENT

Thanks @Eric W - I've tweaked it as per your instruction but still get the wrong answer.

1589921696851.png


Here is my Tenders table. The cell with the formula in is on an different sheet. The formula I'm using (which I've tweak to suit) is

=COUNTBLANK(Tenders!D6:INDEX(Tenders!D:D,AGGREGATE(14,6,ROW(Tenders!$C$6:$C$10000)/(Tenders!$A$6:$A$10000<>"")/ISERROR(SEARCH("TENDER",Tenders!$C$6:$C$10000)),1)))

I'm counting the blanks in Column D. The answer I'm getting is 2 which isn't correct as Tender No 10974 has "TENDER" in Column C so the answer should be 1. I must be missing something obvious!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
It looks like I had a subtle misunderstanding of your request. The formula as shown finds the lowest row that is not empty in column D, and doesn't have TENDER in column C, which is row 11 in your example. Then it counts blanks in column D from 6 to 11, giving you the 2.

It sounds like what you really want is to find the lowest non-empty row in D (11), then count empty rows in D that don't have TENDER in C up to that row. True? If so, try:

=SUMPRODUCT(--ISERROR(SEARCH("TENDER",Tenders!C6:C10000)),--(Tenders!D6:D10000<>""),--(ROW(Tenders!D6:D10000)<=AGGREGATE(14,6,ROW(Tenders!C6:C10000)/(Tenders!A6:A10000<>""),1)))

That's a slightly different take on it, although you can see the SEARCH and AGGREGATE functions still in there. I might be able to improve on this, I'll think about it some more.
 

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
161

ADVERTISEMENT

It sounds like what you really want is to find the lowest non-empty row in D (11), then count empty rows in D that don't have TENDER in C up to that row. True?

Thats spot on - thats exactly what I'm trying to do.

Your suggestion returns a value of 4726 (which is 2 less than the total number of non blanks rows) - the answer should be 0 as it stands. If I make one of the cells blank in D then the number DECREASES by 1. If I make another cell blank which has TENDER in C then the result doesn't change. Its almost working but is returning the total number of non-blank rows (not sure why there is a difference of 2 though).

I'm trying to see where the formula needs tweaking and whilkst I have a bit of an idea with formulas you've gone way overe my head with how you've put this one together!

Thanks for sticking with it. 😊
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
Ugh! I swear my brain is mush sometimes. Try:

=SUMPRODUCT(--ISERROR(SEARCH("TENDER",Tenders!C6:C10000)),--(Tenders!D6:D10000=""),--(ROW(Tenders!D6:D10000)<=AGGREGATE(14,6,ROW(Tenders!C6:C10000)/(Tenders!A6:A10000<>""),1)))

See the equal sign in red? I had <> which was the exact opposite of what you wanted. That should get you much closer. 🤞 Somehow on my test sheet it gave the expected result, so I didn't look much closer. That might also fix the issue of the 2 row difference. If not, we can work through that too.

One question, if the formula doesn't work, is what do you have in column D? Is it an empty cell, or a formula that returns a space, or a null, or is it a space? You'll notice that the latest formula doesn't use COUNTBLANK. I'm not a huge fan of it, since it doesn't count some cells that look blank. So that could be part of our issue.

Sorry this is taking so long, but we'll get there!
 

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
161
Brill, that makes sense - all looks to be working now. Many thanks for your help, i'd have never worked that out
 

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
161
Hi @Eric W - A further consideration has cropped up with I should have foreseen. How would I amend the formula to allow for multiple text strings to search e.g. if the string being search for included "Apple" or "Banana"? The scenario would be to NOT count any blanks that contain either of the text strings (I may want to add in additional ones at a leter date).

I assume I would need to add in an OR statement somewhere and I've gone with the following:-

=SUMPRODUCT(--ISERROR(OR(SEARCH("Apple",Tenders!C6:C10000,"Banana",Tenders!C6:C10000))),--(Tenders!D6:D10000=""),--(ROW(Tenders!D6:D10000)<=AGGREGATE(14,6,ROW(Tenders!C6:C10000)/(Tenders!A6:A10000<>""),1)))

... which inevitably doesnt work!

To answer your earlier question, the "blanks" in column D are always proper blanks as they are empty cells looking for a user input.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,212
Messages
5,640,897
Members
417,177
Latest member
njosh

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