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

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
159
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

Board Regular
Joined
Sep 5, 2013
Messages
159
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
159
Thanks for your persistance Peter - a great help - we got there in the end. Thanks again
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,516
Office Version
  1. 365
Platform
  1. Windows
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],"")
 

Watch MrExcel Video

Forum statistics

Threads
1,119,104
Messages
5,576,140
Members
412,700
Latest member
IIII
Top