• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

countif and sumif

Hi Expert's excel 2007

I have in cell d2 weekly!d6:d50 and in cell h2 =countif(indirect(d2),"")

the count if formula give an answer of 16. When the correct answer is 29..
what's wrong. .
0
route217
Asked:
route217
  • 2
  • 2
2 Solutions
 
Naresh PatelTraderCommented:
Can provide your Sample workbook?
0
 
route217Author Commented:
Sorry cannot upload file from my location.
0
 
Naresh PatelTraderCommented:
try this
=count(indirect(d2),"")

Open in new window



Thanks
0
 
Rob HensonFinance AnalystCommented:
The formula as is works, it will count the number of blanks in the defined range.

Are you sure that all 29 that you are expecting it to count are actually blank. The cells could contain just a space which makes the cell appear to the human eye as blank but Excel sees it as not blank.

Apply an Autofilter to the column and use the dropdown to select Blanks. Those that appear to be blank should show even if they contain just a space; foible of Excel, it counts space as not blank in formulae but shows it in a blank filter.  

If the cells contains just an apostrophe excel will see this as blank in both cases, formula and filter.

You can then highlight the visible cells and press delete key. This will delete the spaces and other non-visible characters.

Thanks
Rob H
0
 
route217Author Commented:
Thanks for feedback.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now