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

Question

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.

Answer 1

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.

Answer 2

you are able to't do it via formulation in trouble-free terms. you need to use the "IF" subject on your formulation, yet which will basically repeat the sparkling, no longer eliminate the line (row). Your in trouble-free terms way of doing so is to place in writing a macro (plug-in seen effortless). i.e. for each each and each row in source column. If no longer sparkling: replica contents in dest column, enhance row of dest column. else do no longer something next

Answer 3

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.

Answer 4

Use countif instead of countblank.

Answer 5

check mrexcel.com.