Importing Data Causing Blank Cells to be Non-Blank

[RudeClown]

Good afternoon,

I am running into an issue where I am importing a dataset from one Excel spreadsheet onto another. On this spreadsheet, I have two tabs, a "Data" tab where the dataset is imported to, and a "Summary" tab where I have formulas set up to summarize the data that has been imported. What I have found is that when the dataset is being imported from its original file onto the "Data" tab into my summarization file, there is something being added to blank cells so they are no longer blank, although they appear to be blank to the naked eye. This causes my summary formulas which involve excluding blank cells to give incorrect counts, as they're counting every cell as non-blank. I have tested, and the cells are indeed truly blank on the original dataset file. Also, if I go to a blank cell within the data on my summarization spreadsheet and hit delete, THEN the cell is counted as blank by my formula. Is there a change I can make within the connection that will fix the import? Or is there something I can do within the formula to account for this? Currently the affected formula is: =COUNTIFS(Data!E:E,"=Open",Data!H:H,"<>") It's column H that is giving me the issue, as the formula is returning the total number of "Open" cells is column E because it's counting every cell in column H as not being blank, even though that is NOT the case. For my purpose, I am counting all rows where column E = "Open" and column H = anything except blank. Any "real" data in column H would be a first & last name, otherwise it would be blank. This formula works perfectly when I copy/paste the dataset, rather than import it. Any suggestions would be greatly appreciated!

 

[rakesh seebaruth]

try this formula

=COUNTIFS(Data!E:E,"=Open",TRIM(Data!H:H),"<>")

 

[RudeClown]

That gives me the "There's a problem with this formula" error.

 

[Fluff]

How about

=COUNTIFS(Data!E:E,"Open",Data!H:H,"?*")

 

[RudeClown]

How about

=COUNTIFS(Data!E:E,"Open",Data!H:H,"?*")

That does seem to work.......what does the "?*" mean? I've not seen or used it before

 

[Fluff]

It means the cell contains one or more characters, although it only works with strings, not numbers.

 

[RudeClown]

It means the cell contains one or more characters, although it only works with strings, not numbers.

Ok, that's perfect for this scenario. Thank you for the explanation. I truly appreciate the help!

 

[Fluff]

You're welcome & thanks for the feedback.