Say for eg.
i want to add a1:g1 in H1, only if all the cells contain numbers. If any of the rows , contains text, like f1 d1 any text, i want the result to be 0.
would you help
Say for eg.
i want to add a1:g1 in H1, only if all the cells contain numbers. If any of the rows , contains text, like f1 d1 any text, i want the result to be 0.
would you help
Here's one way using an array formula
Remember its an array formula so enter it with Control + shift + enter (not just enter)Please Login or Register to view this content.
Happy with my advice? Click on the * reputation button below
Hi
try this:
note this treats blanks as numerical value zeroPlease Login or Register to view this content.
Hi, Both formula works , but if you could write, how it works, for eg, crooza using 25, and nicky using istext and greater than 0, it will be useful.
This may be easier to understand...
A B C D E F G H 1 1 2 3 4 5 6 7 28 2 1 2 3aa 5 6 7 0
H1=IF(COUNT(A1:G1)=COUNTA(A1:G1),SUM(A1:G1),0)
copied down
COUNT() only counts numbers
COUNTA() counts everything
so, test 1 against the other - if they match, all entries are numbers (even numbers generated from a formula)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi, i do not want count function.
I am only using count function ti see if there is any text in that range - if there is (per your request) it returns 0, otherwise it sums teh range
Mine was just a quick check to see if each cell contains a number. It was quick and nasty and won't capture negatives either. The 10^25 is just a really big number. You could change the <0 to <-10^25 to capture negatives too although I'm the first to admit some of the other solutions here are probably more robust
Hi, Nicky,
Please say, what your formula does in brief. pl.what is that multipled by 1.
please
Hi, I think Nicky is not around. anybody gives how nickys formula works. Particularly why 1 is used and what is istext?
I'll do my best
ISTEXT returns TRUE or FALSE depending on whether a cell contains text
The SUMPRODUCT around this looks at the array of cells A1 to G1 and returns a string of TRUE/FALSE values, depending on whether the cells contain text. So if you had say 2, aa, 3, 1, bb, 4, 5 as values in each of those cells (A1 to G1) the array would look like FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE. Multiplying by 1, changes these to values 0,1,0,0,1,0,0 so the answer is 2.
The IF formula is checking to see if this answer (2) is greater than 0. If it is, then the result is 0. If it isn't >0, then there is no text so the numbers are added together using the SUM part of the IF formula.
As a tip, if you ever want to see what a formula is doing, select the formula cell, go to the Formulas tab and click on the Evaluate button. You can then step through the formula as it calculates the result.
Hope that helps your understanding a little.
Excel is a constant learning process and it's great to help each other. If any of us have helped you today, a click on the "reputation" star on the left is appreciated.
Hi, Shirley Thanks doubly for your explanation.
If you are excel in maths and programming , it may be easy to understand. But , when the solution is given with the notes, as you have given, would immensely benefit the users of excel who are applying the formula.
sub query: would this work for negative numbers , or should i change slight. Please give the changed formula for that also, ofcourse, without explanation.
Your teaching really excels. can i expect a reply
Hi,
Thanks for the positive feedback - part of my job is teaching Excel so always good to know my explanations help
The formula will work exactly the same with negative numbers - no need to change anything. The negative numbers will just be included in the SUM part of the formula when that is applied (i.e. when no cells in the row contain text)
Hi, could you say, how the expert think of giving this thought, like coining the formula in such way that no oversight is possible, like multiplying by 1 to add and then check if it equals 0 to give the required result otherwise 0. where this trick of deep analysis in mind could be stored. Really amazing site to learn excel. With your reply, will solve this thread
I'll leave that one to the forum admin
Thanks for the feedback and happy to have helped
hi, Thanks for all who helped me to resolve.
My appeal to forum admin to ask the experts to give solution as formulas with a little more logical explanation
This is definitely one of the best sites to find out about Excel - even after many years of working with Excel I still find all sorts of new things
When I pick up a new method or tip, I keep an example or two in a workbook so I can easily refer back to it. I include with this any explanations which have been provided - it's easy to forget things if you don't come across them very often. Also, by creating examples it helps to make the techniques stick in your mind so the next time you come across a similar problem, you will know you have a solution, even if you have to go back to your notes for the exact details.
But if you're ever unsure, the forum is always a great place to seek help and advice
Edited version, unlike my first suggestion, this allows 0 values and empty (but not blank) cells.
=IFERROR(SUMPRODUCT(A1:G1/1),0)
If you want to understand how a formula works, go to the 'Formulas' tab on the Excel ribbon, then use the 'Evaluate' function.
Complex formulas might need an explanation from the person providing it, but with simpler formulas like this, you will probably learn more from trying to figure it out yourself.
Last edited by jason.b75; 05-28-2016 at 11:39 AM.
Here is another way with use of somewhat forgotten function ISNA()
Formula:Please Login or Register to view this content.
v A B C D E F G H 1 45 text 45 text text 5 45 0 2 45 50 45 4 45 5 8 202
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
This also might work for you.
Formula:Please Login or Register to view this content.
The ISTEXT examines result of the LOOKUP in the range A1:G1 and if text is found, the LOOKUP returns a text value. This makes ISTEXT return TRUE. The TRUE then returns the TRUE part of the IF function. If text is not found ISTEXT returns FALSE and the FALSE part of the IF function is returned.
A B C D E F G H 1 1 2 4 6 5 6 6 30 2 1 2 v 6 4 7 9 0 3 g 1 2 3 4 5 6 0 4 9 8 7 6 5 4 3 42
Last edited by newdoverman; 05-28-2016 at 11:30 AM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Something else that just came to mind
=IFERROR(SUMPRODUCT(SMALL(A1:G1,COLUMN(A1:G1))),0)
Although, like others, I fail to see what is wrong with the suggestion in post #5, it's simple and it works, so why dismiss it?
Hi rajuj
sorry I did not reply to your earlier thread, for some reason I am no longer receiving notifications of new comments on subscribed threads, and didn't realised you has posted a question. Looks like Shirley answered your question, though: thanks Shirley!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks