Can a countblank function (in Excel) count blank cells in Column C: only if cells column A have data?

Here is an example:





A B C D


1 dog test 123 help


2 cat help 111 help


3 bat


4 frog


5


6





Let's say, I wanted to count the total number of blank cells in Column D, but only if there is data on the same matching rows on Column A. Since there is data in A3 and A4 and no data in D3 and D4, the formula should report 2. The trouble that I am running into is that if I perform a CountBlank in D:D, Excel will give me a total that includes all cells from D3 to all the way down from this column. Is there anyway to set this up so that it will only provide a total count of empty cells in Column C to the corresponding data in Column A?, unless I add data to A5 (and additional data further down Column A), or if I update the spreadsheet later and add data into D4 or later rows (so that these are not counted)? Any assistance you can provide is greatly appreciated.

Can a countblank function (in Excel) count blank cells in Column C: only if cells column A have data?
Well, I would create a hidden column with the following formula:





=IF(ISBLANK(D2),IF(ISBLANK(A2) ,1,0) ,1)





Basically, it'll set the column to 1 only if D2 is blank and A2 is not. Otherwise the column is set to zero.





You can then add a sum to this column to give the number of rows that meet your criteria.
Reply:Well, you got the idea. :) Report It

Reply:Use countif instead of countblank.
Reply:I like BigRez's approach, but his formula actually yields the opposite of what you want. Here's a formula that does work, and at least for me, is a little easier to understand:





=IF(AND(ISBLANK(D1),


NOT(ISBLANK(A1))),1,0)





It's split on two line to display properly here. You can copy and paste it into cell E1, then backspace to eliminate the line break.
Reply:check mrexcel.com.