I have tried
but Excel merely treats "not(isformula)" as text to be compared, not as a condition. Can this be done with SUMIF? If not, is there another function?Please Login or Register to view this content.
I have tried
but Excel merely treats "not(isformula)" as text to be compared, not as a condition. Can this be done with SUMIF? If not, is there another function?Please Login or Register to view this content.
Welcome to the forum.
That won’t work, as you have found. What does the formula in question determine?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi,
For Excel 2007 you'd have to save the workbook as macro-enabled, since then you could employ the following definitions within Name Manager:
Name: MyRange
Refers to: $A$1:$A$5
(Or whatever happens to be the range in question.)
Name: HasFormula
Refers to: =GET.CELL(48,OFFSET(INDEX(MyRange,1),N(INDEX(ROW(MyRange)-MIN(ROW(MyRange)),,)),))
You can then use the following formula within the worksheet:
=SUMPRODUCT(1-HasFormula,MyRange)
Regards
Thanks for your reply. I have data copied from another sheet, with column totals somewhere below the last data. I am trying to ignore the formula-generated value, and it seemed to me that SUMIF was exactly what I needed. If SUMIF can't handle the ISFORMULA condition, I can easily find a work-around; I was asking primarily to add to my understanding of EXCEL.
OK - I am not sure you understood my question, but it seems you have a solution offered above.
XOR LX: Thanks for your solution; this approach would never have occurred to me. Thanks also for introducing me to the GET.CELL function
GET.CELL is not a function but a macro. See http://www.sulprobil.com/Get_it_done.../get_cell.html
If you use it you must save your file as xlsm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks