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.

 

[drewberts]

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?

Yes

What I've got is a sheet that creates a list of job numbers using ROWS()+... into a hidden column called JobNo1. The formula in Col A then replicates that job number in each cell on each row and sets it as a hyperlink back to the corresponding row in the JobTable - This hidden column doesnt have any blanks at all as it uses ROWS() to generate the number sequence. The formula in Col C checks if the Col A cell is blank before looking for the Date Confimed from a column in JobTable. If the JobTable doesn't have a value in this cell it returns a blank - I'm trying to track the blanks and summarise how many are missing

*STOP PRESS*

Just thinking this through (for the umpteenth time!) - There is no reason why I can't apply your formula to the JobTable (not the TabJobTrack table) as the same info is in this table (albeit a different configuration). Just done that and the COUNTIF formula returns a strange number but the SUMPRODUCT formula works!

Brill

 

[Peter_SSs]

the SUMPRODUCT formula works!

Great!

I will ask around to see if I can find out more about what is going on with COUNTIFS and the tables.

 

[drewberts]

Thanks for your persistance Peter - a great help - we got there in the end. Thanks again

 

[Peter_SSs]

You're welcome.

 

[Peter_SSs]

I will ask around to see if I can find out more about what is going on with COUNTIFS and the tables.

It seems not related to table but how COUNTIFS (& COUNTIF) works with counting/not counting "" returned by formulas & I was using the wrong syntax.

So although SUMPRODUCT is doing the job, I'm interested if this works too for you?

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