Count No of Blanks Cells if a cell on same row is Blank

[drewberts]

I have a table set out the same as below:-

A B C D E F
1 JobNo First Name Last Name Site Postcode Date Started
2 1000 Tom Rogers 1 ABC Road L1 123 01/010/20
3 1201 Dave Smith 2 ABC Road L2 123
4 Steve
5 1450 Frank Jones L3 123
6 1650 Susan Roberts 5 ABC Road L4 123 01/010/20

In a separate cell I want to add a formula that counts the number of blank cells in say Col C but only if there is a value in col A. So in the example above the answer would be 0 for column C as A4 is blank. If we used the formula on col D then the answer I'm looking for is 1 as D5 is blank but there is a value in A5, but it ignores D4 as there is no value in A4.

The real sheet has 100's of lines and what i'm trying to do is set up some cells to tell me how many rows have blank data in them on a column by column basis, but only if there is a value in the corresponding cell on each row in column A.

I'm thinking its a sumproduct (or maybe a countif) coupled with an IF statement that somehow looks at each row??

Hope that makes sense.

 

[Peter_SSs]

Try this, copied across

Book1
	A	B	C	D
2	x	x	x	x
3	x	x	x	x
4		x
5	x	x	x
6	x	x	x	x
7
8			0	1
Count blanks

Cell Formulas
Range	Formula
C8:D8	C8	=COUNTIFS($A2:$A6,"<>",C2:C6,"")

 

[drewberts]

@Peter_SSs

Many thanks for your fast response. Apologies at my end as your solution works perfectly but I'd missed a crucial element out. Colum A isn't actually blank - every cell has a formula in it but some return a blank. Apologies as I'd assumed that returning a blank was the same as a cell being blank - clearly not.

How can the formula be tweaked so that using your example above the result is the same with range A2:A6 all containing a formula but A4 returns a blank (rather than the cell being blank)

Sorry for the lack of clarity

Thanks

 

[Peter_SSs]

Try this instead
=COUNTIFS($A2:$A6,"<>""",C2:C6,"")

 

[drewberts]

@Peter_SSs

Thanks again for your fast response - that works as well! - BUT I'm failing miserably to explain my situation properly - apologies once again as the result isn't what I'm after (but thats entirely my fault for my naff explanation)

Let me have another go - I can see where my problem now is as Col C is also not "blank", it has formulas that are returning blanks as well - I can now see that Excel sees all of Col A and all of Col C as non blank cells because they all contain formulas. The end user is seeing blank RESULTS rather than blank cells, hence my problem.

Therefore Excel doesnt see any blanks at all across both columns (unless you manually delete a formula and then your solution(s) work) - I can see that now. So I suppose the real question is how to get the results I'm after by looking for the blank formula results rather than blank cells.

The formulas in both A and C are using IFERROR which is why I'm getting "blank" results.

Sorry for the confusion - you have my permission to slap me around the face!

 

[Peter_SSs]

Having formulas is not likely to be the problem at all. More like is the formulas themselves. Perhaps you could post a formula from column A & one from column C (copy and paste them in the forum, don't type it here manually)

In this sheet I have formulas in columns A, C & D and the count is still working.

Book1
	A	B	C	D
2	x	x	x	x
3	x	x	x	x
4		x
5	x	x	x
6	x	x	x	x
7
8			1	2
Count blanks

Cell Formulas
Range	Formula
C2:D6	C2	=IF(F2="","",F2)
A2:A6	A2	=IF(F2="","",F2)
C8	C8	=COUNTIFS($A2:$A6,"<>""",C2:C6,"")
D8	D8	=COUNTIFS($A2:$A6,"<>""",G2:G6,"")

 

[drewberts]

Thanks Peter - red herring on my part then. My table is called TabJobTrack and the formula I'm using from you is

=COUNTIFS(TabJobTrack[Job No],"<>""",TabJobTrack[Date Confirmed],"")

This table has 493 rows of data. The 493 rows of Col A all contain formulas as do trhe 493 rows of Col C. 105 rows have a visible value (i.e. non blank) in col A and 101 of these 105 have visible values in Col C, meaning I have 4 rows with a value in Col A but not in Col C, so I'm looking for the formula to return 4 as an answer. The answer I'm getting is 388 which is the no of rows that are blank in both Col A and Col C


Col A is called [Job No] in table header row
Formula is
Col A =IFERROR(HYPERLINK("#" &@ CELL("address",INDEX(JobTable[Job No],MATCH([@JobNo1],JobTable[Job No],0))),[@JobNo1]),"")

Col C is called [Office Started] in table header row
Formula is
Col C =IFERROR(IF($A2="","",(INDEX(JobTable[Office Started],MATCH([@[Job No]],JobTable[Job No],0),1))),"")

 

[Peter_SSs]

=COUNTIFS(TabJobTrack[Job No],"<>""",TabJobTrack[Date Confirmed],"")

Col A is called [Job No] in table header row
Formula is
Col A =IFERROR(HYPERLINK("#" &@ CELL("address",INDEX(JobTable[Job No],MATCH([@JobNo1],JobTable[Job No],0))),[@JobNo1]),"")

Col C is called [Office Started] in table header row
Formula is
Col C =IFERROR(IF($A2="","",(INDEX(JobTable[Office Started],MATCH([@[Job No]],JobTable[Job No],0),1))),"")

The blue parts seem to agree, but the red does not. Can you clarify? (You may need to 'Click to expand...' under the quote above to see all I have highlighted)

 

[drewberts]

Apologies - one of those days. Should read...

Col C is called [Date Confirmed] in table header row
Formula is
Col C =IFERROR(IF($A2="","",(INDEX(JobTable[Date Confirmed],MATCH([@[Job No]],JobTable[Job No],0),1))),"")

 

[Peter_SSs]

=IFERROR(IF($A2="","",(INDEX(JobTable[Date Confirmed],MATCH([@[Job No]],JobTable[Job No],0),1))),"")

Is the $A2 in this formula referring to a cell in the 'Job No' column of table 'TabJobTrack' on the same row as the formula itself?

I'm trying to set up something similar to what you might have but I'm struggling to get those 2 formulas to produce any rows where column A or C has data but the other does not.
However, I do seem to be getting some strange results using COUNTIFS on the table.

You could also try this and see what happens

=SUMPRODUCT(--(TabJobTrack[Job No]<>""),--(TabJobTrack[Date Confirmed]=""))