How to count a cell that looks Blank as Blank despite it containing formula

P

PaulyK

Board Regular
Joined
Aug 27, 2015
Messages
50
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi,

Trying to Produce a summary to show if data has been entered each month for each project.
I have tried several ways to count all blank cells (even containing formula) as blank. However I can't seem to crack it.

The end point is to summarise if data has been entered in a particular month in my 'Non-Compliance' table and then be able to pull that result (yes or No) into a separate Summary for the month.

Data Source
Count Blanks Example.xlsm
ABCDEFG
1PROJECT NUMBERPROJECT NAMEApr-22May-22Jun-22Jul-22Aug-22
21001Project 161
31001Project 11
41001Project 181010106
51002Project 2105
61002Project 2155
71002Project 25551
81003Project 3
91003Project 3
101003Project 3
111003Project 35
121003Project 32020202020
131004Project 4
141004Project 4105
151005Project 5
161006Project 6
171006Project 60.50.50.50.50.5
181007Project 71
191008Project 855
201008Project 8345
211008Project 8
221008Project 85
231008Project 8505
241008Project 8
251008Project 8
261009Project 92
271009Project 91
281009Project 90.500
291009Project 9
301009Project 92020202020
311010Project 108
321010Project 100
331010Project 101020202020
341010Project 101020202020
351010Project 101020202020
361011Project 1188888
371011Project 111414
381012Project 1200
391012Project 12
401013Project 133321
411013Project 131
421013Project 135421
431013Project 13362
441013Project 131
451014Project 141080
461014Project 1441
471014Project 146
481014Project 1488
491014Project 1443
501014Project 14
511014Project 14
521014Project 14155
531014Project 14
541014Project 14
551014Project 14
561014Project 1422
571014Project 14
581015Project 151
591015Project 1545
601015Project 15
611015Project 1500
621015Project 1554222
631016Project 168101084
641017Project 17
651017Project 1744
661017Project 17444
671017Project 17877124
681017Project 1799662
691017Project 171210662
701017Project 1721
711018Project 18
Project allocation
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:G71Expression=COUNTBLANK(C2)textNO


Monthly Allocation Summary
Count Blanks Example.xlsm
ABCDEF
1PROJECT NAMEApr-22May-22Jun-22Jul-22Aug-22
2Project 115.011.010.010.06.0
3Project 230.015.05.01.0
4Project 325.020.020.020.020.0
5Project 410.05.0
6Project 5
7Project 60.50.50.50.50.5
8Project 71.0
9Project 818.04.015.0
10Project 923.520.020.020.020.0
11Project 1038.060.060.060.060.0
12Project 1122.022.08.08.08.0
13Project 12
14Project 13 12.014.06.02.0
15Project 1449.027.0
16Project 1510.09.02.02.02.0
17Project 168.010.010.08.04.0
18Project 1739.035.023.024.08.0
19Project 18
Monthly Project Allocation
Cell Formulas
RangeFormula
B2:B19B2=IF(SUMIFS(TblProjAlloc[Apr-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)=0," ",(SUMIFS(TblProjAlloc[Apr-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)))
C2:C19C2=IF(SUMIFS(TblProjAlloc[May-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)=0," ",(SUMIFS(TblProjAlloc[May-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)))
D2:D19D2=IF(SUMIFS(TblProjAlloc[Jun-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)=0," ",(SUMIFS(TblProjAlloc[Jun-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)))
E2:E19E2=IF(SUMIFS(TblProjAlloc[Jul-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)=0," ",(SUMIFS(TblProjAlloc[Jul-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)))
F2:F19F2=IF(SUMIFS(TblProjAlloc[Aug-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)=0," ",(SUMIFS(TblProjAlloc[Aug-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F19Expression=ISBLANK(B2)=TRUEtextNO
B2:F19Celldoes not contain a blank value textNO


Non-Compliance (Displays YES for all currently - but shouldn't!)
Count Blanks Example.xlsm
ABCDEFG
3Project IDNameApr-22May-22Jun-22Jul-22Aug-22
41001Project 1YesYesYesYesYes
51002Project 2YesYesYesYesYes
61003Project 3YesYesYesYesYes
71004Project 4YesYesYesYesYes
81005Project 5YesYesYesYesYes
91006Project 6YesYesYesYesYes
101007Project 7YesYesYesYesYes
111008Project 8YesYesYesYesYes
121009Project 9YesYesYesYesYes
131010Project 10YesYesYesYesYes
141011Project 11YesYesYesYesYes
151012Project 12YesYesYesYesYes
161013Project 13YesYesYesYesYes
171014Project 14YesYesYesYesYes
181015Project 15YesYesYesYesYes
191016Project 16YesYesYesYesYes
201017Project 17YesYesYesYesYes
211018Project 18YesYesYesYesYes
Non-Compliance
Cell Formulas
RangeFormula
C4:C21C4=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[Apr-22],"<>"),"Yes","No")
D4:D21D4=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[May-22],"<>"),"Yes","No")
E4:E21E4=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[Jun-22],"<>"),"Yes","No")
F4:F21F4=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[Jul-22],"<>"),"Yes","No")
G4:G21G4=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B4,TblTime[Aug-22],"<>"),"Yes","No")
A4:A21A4=IFERROR(INDEX(TblProjAlloc[PROJECT NUMBER],MATCH([@Name],TblProjAlloc[PROJECT NAME],0))," ")
Named Ranges
NameRefers ToCells
'Project allocation'!_FilterDatabase='Project allocation'!$A$11:$B$50A4:A21
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C4:G21Cell Value="No"textNO
C4:G21Cell Value="Yes"textNO


Monthly Summary (this ideally would also be able to select the month column relevant to the current month today - not sure how to achieve)
Count Blanks Example.xlsm
JKL
3ProjectUpdatedMonth
4Project1NoJune
5Project2NoJune
Non-Compliance
Cell Formulas
RangeFormula
K4:K5K4=IF(COUNTIFS(tblNonCom16[Name],[@Project],TblTime[Apr-22],"<>"),"Yes","No")
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Try
Excel Formula: 
=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B8,TblTime[Apr-22],">0"),"Yes","No")
but I would advise changing the formula in TblTime to
Excel Formula: 
=IF(SUMIFS(TblProjAlloc[Apr-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)=0,"",(SUMIFS(TblProjAlloc[Apr-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)))
You should not add a space if you want it blank.
 
Upvote 0
Solution
Fluff said:
Try
Excel Formula: 
=IF(COUNTIFS(TblTime[[PROJECT NAME]:[PROJECT NAME]],$B8,TblTime[Apr-22],">0"),"Yes","No")
but I would advise changing the formula in TblTime to
Excel Formula: 
=IF(SUMIFS(TblProjAlloc[Apr-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)=0,"",(SUMIFS(TblProjAlloc[Apr-22],TblProjAlloc[PROJECT NAME],"="&'Monthly Project Allocation'!$A2)))
You should not add a space if you want it blank.
Click to expand...
ah I see. Thank you
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
How to make this IF formula work?
Replies
1
Views
237
Fluff
Fluff
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
233
arun_eagertolearn
A
R
  • Question Question
Help to sum all cells above until blank cell
Replies
3
Views
664
RemyMoo
R
M
  • Question Question
Excel formula/syntax question
Replies
4
Views
456
mkrass
M
M
  • Question Question
Change value of cells in a range based on value of another cell
Replies
7
Views
342
mkrass
M

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

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