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

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
177
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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,"")
 
Upvote 0
@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
 
Upvote 0
Try this instead
=COUNTIFS($A2:$A6,"<>""",C2:C6,"")
 
Upvote 0
@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!
 
Upvote 0
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,"")
 
Upvote 0
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))),"")
 
Upvote 0
=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)
 
Upvote 0
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))),"")
 
Upvote 0
=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]=""))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top