ISBLANK / ISTEXT Question

[Deb6508]

Hi All,
I am running into a problem and can not figure out why this does not work now. Originally I was inputting info into a spreadsheet but when I realized I would now have thousands of records I created an Access database. After gathering all of the information I just copied the info from Access back to the spreadsheet pulling only the dates I needed. When using only the data from the spreadsheet this calculation worked perfectly

=SUMPRODUCT(--('FSC 06-26-2010'!$U$5:$U$4999=$A9),--(ISBLANK('FSC 06-26-2010'!$R$5:$R$4999)))

but now with the data from the database copied over it is not summing correctly. I have double and triple checked all of the columns and rows to make sure they remained the same, but I can not for the life of me figure out where my problem lies. In a perfect world the second part of this equation would count Y's or N's in column Q under the FSC 06-26-2010 tab but because it is a drop down box I don't believe that is possible.

Does anyone by chance see an error with my calculation?

Any help would be greatly appreciated!!!!

Thanks so much for looking at my question.

Deb

 

[VoG]

Maybe the cells are not blank. Does this return the number that you expect?

=COUNTBLANK('FSC 06-26-2010'!$R$5:$R$4999)

 

[pgc01]

Hi

Try:

=SUMPRODUCT(--('FSC 06-26-2010'!$U$5:$U$4999=$A9),--('FSC 06-26-2010'!$R$5:$R$4999=""))

 

[Deb6508]

Hi Peter,
Thanks for the tip. I received the number that I needed. Just to be sure I copied a blank cell from another part of the spreadsheet into the others, but I am still having the same issue.

There doesn't seem to be any consistancy as to what is cunted and what isn't either.

For example for one team I have a blank, text, blank in column R and the sum I get is 3 Texts and 0 Blanks.

Another team I have all blanks but they show up as all Texts.


Deb

 

[Deb6508]

Hi pgc01,
YEAH!!!!! That did it!!! Thank you so much!!!!

I really appreciate the help!!!

Deb

 

[pgc01]

Hi Deb

I'm gald it helped.

There doesn't seem to be any consistancy as to what is cunted and what isn't either.

For example for one team I have a blank, text, blank in column R and the sum I get is 3 Texts and 0 Blanks.

Another team I have all blanks but they show up as all Texts.

Using the term "Blank cell" is dangerous in excel, as it may have 2 different senses.

Maybe post #10 in this thread will help you to understand what's happening in your case:

http://www.mrexcel.com/forum/showthread.php?t=538637#10

Check also post#9 from Biff in the same thread.