Excel problem

Im sure its simple but....

[Essex_boy]

I have 5 fines columns of 1's
1 1 1 1 1 I can add the figures up and get 5 in the 6th column, if I delete one I get a total of 4, what I wantto do is to be able to the result 5 in column 6 regardless of what I put in place of the figure one.

How do I do it ?

[ergophobe]

You're looking for the COUNTA function

[support.office.com...]

[Essex_boy]

Hmm not really it counts the number of rows etc I need to count the figures 1 1 1 1 1 = 5 places 5 in another column. Deleting or adding any numbers to the 1's will not alter the 5 given.

[ergophobe]

I'm not following you at all. Aren't you trying to count the number of non-empty cells in a range?

So if you have 1 1 1 1 1 it should give you 5

But if you have 1 2 45 3 1 based on what you said you still want a total of 5

But then you say that no matter what's in the first five columns, you want the result in column 6 to always be five.

That's easy. In column six just type the number "5" - that will give you a five in column six no matter what's in the first five columns

[BTW - what is a "figure" - I know what numbers, numerals, cells, values, formulas are. To me a figure is a graph or table in an article]

[LifeinAsia]

just type the number "5"

I think you almost have it... The OP wants a formula, so he should actually use "=5" in the cell. :)

[tangor]

The original answer was almost correct. Over a range (columns a1,b1,c1,d1,e1 or rows a1,a2,a3,a4,a5) the formula will include (summed) the sum of the integers, AND the COUNTBLANK (not COUNTA) of the same range. When both are added together that number will always be five (5) assuming that the integers in each non-blank cell are always "1".

=SUM(A1:E1)+COUNTBLANK(A1:E1)

[ergophobe]

Ah... is that what he was asking for? Could be. But then you wouldn't get "5" in all cases. Anyway, one or the other will work.

No offense Essex_Boy, but I feel like answering this question is like writing an article based on pronouncement from the US Federal Reserve Board ;-)

[LifeinAsia]

I'm still confused about exactly what is being asked.

EB- please provide some specific examples, like:
1 1 1 1 1 = 5
2 2 2 2 2 = 5
1 1 1 1 _ = 5

[tangor]

I'd like to know the purpose of a formula that is always one result, ie, can't be proved or disproved .... but if there is a purpose, the formula above is one way to get that result.

If, however, a result other than "5" is a call for action elsewhere on the spreadsheet, that might mean something. Just don't have enough info on the "why" ....

[Essex_boy]

Lifeiasia:

1 1 1 1 1 = 5
1 2 1 1 1 = 6
7 1 1 1 1 = 11

But once this final figure (5, 6 or 11) is given altering any of the other numbers will not alter the final figure (5, 6 or11) I might need a macro....

[Essex_boy]

Tangor: Not right ! I want the answer to be fixed not fluid

[ergophobe]

So basically what you are looking for is sort of like the difference between "paste" and "paste values"

Hmmm... I don't have a clue how to do that. I think you'll have to look into VBA and figure out whether or not you can lock a cell.

[tangor]

Where are these numbers coming from and what is the necessity for calculation results to be 5, 6, or 11? Once again, purpose for results is necessary to determine what formula(s) are required. What are you "counting"?

As for forcing a result of 5,6, o 11 an additional IF test for those values, rejecting all others, can be added to the formula and would be less time consuming than creating a macro..

[edited by: tangor at 10:08 pm (utc) on Oct 20, 2016]

[LifeinAsia]

Sounds like do a SUM of each row to get the values, then copy and paste (values) to another column. Then if you change any of the values, the SUM column will change, but not the column you pasted into.

[ergophobe]

>>not the column you pasted into

With a normal paste, this would paste the formula offset for the column change, so if you're using tangor's formula
=SUM(A1:E1)+COUNTBLANK(A1:E1)

in cell F1 and you paste it into cell G1, it will paste (I believe) as
=SUM(B1:F1)+COUNTBLANK(B1:F1)

But as tangor says, I think we need to understand what EB is trying to achieve, because it may make more sense to rethink the problem a bit.

[LifeinAsia]

in cell F1 and you paste it into cell G1, it will paste (I believe) as
=SUM(B1:F1)+COUNTBLANK(B1:F1)

Not if you paste the values (like I mentioned)- select the cell(s) you want to copy, move to the target cell, then right-click and select Paste Values.

[ergophobe]

Oh sorry... I missed the "(values)" in your post...

This is going to turn into a 79-post thread by the time we're through with it ;-)

[Essex_boy]

Ha ha, figures given are general.

Im added many figures over many thousands of rows, once added up I need to remove the calculation figures (over 6 rows) and leave the result which is then added to a database.... But once the calculation figures are rmove the total reverts back to zero.

Thats my problem, I needed a macro