HI and thanks to all the people here for always be ready to help!
I was wondering if someone can help me with a formula.
I am using this formula =IF(J$4="","",(SUM(K5*M5,S5*U5,AA5*AC5,AI5*AK5,AQ5*AS5,AY5*BA5,BG5*BI5))) which works if every cell has a number, however once one is empty it returns a value error.
Is there a way to have it do the same but ignore the cells that are empty?
You could try:instead is SUM(...)Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Your "empty" cells aren't really empty. check them: =ISBLANK(cell). Probably there is something like ="" or result from another formula is ""
I cannot replicate the scenario. When I set up a similar sum of products formula and clear one of the cells, that pair is simply not included in the sum. It works this way because an empty cell is assumed to be 0, so that part of the sum becomes 0.
Reading between the lines, we often use the word "empty" for a cell that contains a formula that returns null string "" -- even though technically this cell is not empty. If I enter a text string (including "" null string) into one of these cells, then I get the error value as you indicate. If this is the case, I would first reconsider using the null string value. If one returned 0 instead of "", then the sum of products formula that you are using should work correctly. There are ways to hide 0 values, if you just cannot stomach looking at that ugly goose egg. IMO, changing the null string to 0 would be the easiest way to resolve this error case.
Of course, if I am wrong and something else is causing the error, then disregard this.
Originally Posted by shg
does not work, I left BG5 and BI5 empty and it returned the value error. I know the =product ignores empty cells but since my cells are not consecutive I can's make it work either.
The cells that are "empty" are not empty in fact they have also a formula
[ deleted by me ] because there are formulas
Please post a sample workbook with some typical data so we can see what your data and formulae look like.
Use N() formula:
=IF(J$4="","",(SUM(N(K5)*N(M5),N(S5)*N(U5))
Thks! the last one worked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks