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
=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