PDA

View Full Version : Sum Function



Ray Whalley
2016-12-18, 10:08 PM
The sum function will not calculate the sum of a range of cells containing numbers resulting from a range of IF functions. How can I correct this?? Ihave tried all the recommended soluyion to no avail.




Q1
4
0
0
0
0
0
0

0


Q2
5
1
1
1
1
1
5

1


Q3
6
1
1
1
1
1
5

1


Q4
7
1
1
1
1
1
5

1


Q5
8
1
1
1
1
1
5

1


Q6
9
1
1
1
1
1
5

1









25

?

AliGW
2016-12-18, 11:19 PM
What is the formula you are using and where? If the final column, then there is a clue in the fact that the numbers are aligned left: thus suggests that they are text, not numbers.

Ray Whalley
2016-12-18, 11:35 PM
I have already checked the cell and they are set on "general" .I have also set them as number with the same result.
The formula used to calculate the cells in question is =IF(AB46>=1,"1",IF(AB46<1,"0")) AB is the row with the 0.5.5.5.5.5.

AliGW
2016-12-18, 11:40 PM
See if this makes any difference:

=IF(AB46>=1,1,IF(AB46<1,0))

Notice no inverted commas around the numbers.

Ray Whalley
2016-12-19, 09:59 AM
See if this makes any difference:

=IF(AB46>=1,1,IF(AB46<1,0))

Notice no inverted commas around the numbers.

This enables the Auto Sum to activate and I have achieved this previously by manually inserting a sum formula. Unfortuanately whilst the formulas say it is adding the relevant cell range containing figures 0.1.1.1.1.1 it is showing a total of 1 not 5.??

Pecoflyer
2016-12-19, 10:47 AM
Please post a small sample sheet (click Go advanced - Manage attachments). Thanks

BTW, please do not quote entire posts unnecessarily. They make the thread hard to follow.
Shorter version of Ali's formula =--(AB46>=1)