Ok In One Column - #VALUE In the Next

S

Singinbeauty

Ok, so here's the deal. I have the formula (example below) in column 'P' and
everything works fine BUT the moment I add a column in and this one moves to
column 'Q' it returns a #VALUE error. A little information about my
spreadsheet... I have a different spreadsheet done up for each sales person
that is attached to and Access Database. On this particular spreadsheet I am
totalling sales and trying to figure out who has done the data input and who
hasn't. Hence the formula below. Each of my colums in this spreadsheet are
pretty much the same but instead of 'Tonya Hayden' there are the different
salespeople's names and they all work except hers and they are all identical
besides the names! HELP!!!!!

=IF('Tonya Hayden'!B2:p2="","",SUM('Tonya Hayden'!B2:p2)

Please, I know it may be a simple stupid answer but I am going nuts here...
any light you can shine on it would be so appreciated! Thank you!
 
S

Singinbeauty

That ALMOST works... It returns a '0' if all of the cells are blank but I
need the cell itself to remain blank if they are blank. Does that make any
sense? hehe. Thank you so much for your help so far in figuring this out...
it's driving me nuts!
 
P

Peo Sjoblom

The formula you got does not work, it will not look at the whole range

=IF(COUNTBLANK(B2:p2)=15,"",SUM(B2:p2))

note that the cells have to be blank either as in empty or as a result of
another formula where "" would be returned, no fake blanks using space key

--
Regards,

Peo Sjoblom

(No private emails please)
 
S

Singinbeauty

This helped so much! Thank you!!! It was driving me MAD... I am curious
though as to why the other formulas worked with my original one but only this
one didn't... Would you know why? Thank you!
 
P

Peo Sjoblom

I believe it was pure coincidence, the formula you posted first will not
look at the whole range, only the first cell so I can only assume that when
it worked the first cell was not blank, you can test that by using

=B2:p2=""

will return TRUE if for instance C2 has a number

the value error occurs if you calculate with a text value, SUM(range) will
ignore it


--

Regards,

Peo Sjoblom
 
Top