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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,513
Office Version
  1. 365
Platform
  1. Windows
Try this, copied across

Book1
ABCD
2xxxx
3xxxx
4x
5xxx
6xxxx
7
801
Count blanks
Cell Formulas
RangeFormula
C8:D8C8=COUNTIFS($A2:$A6,"<>",C2:C6,"")
 

drewberts

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,513
Office Version
  1. 365
Platform
  1. Windows
Try this instead
=COUNTIFS($A2:$A6,"<>""",C2:C6,"")
 

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
159

ADVERTISEMENT

@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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,513
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
2xxxx
3xxxx
4 x  
5xxx 
6xxxx
7
812
Count blanks
Cell Formulas
RangeFormula
C2:D6C2=IF(F2="","",F2)
A2:A6A2=IF(F2="","",F2)
C8C8=COUNTIFS($A2:$A6,"<>""",C2:C6,"")
D8D8=COUNTIFS($A2:$A6,"<>""",G2:G6,"")
 

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
159

ADVERTISEMENT

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

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,119,061
Messages
5,575,872
Members
412,689
Latest member
nhsmedic
Top