Sums

[Jemma]

Is there any way of only working out a sum in the boxes that are used and
discluding the boxes that haven't been used. For example i have a worksheet
which includes a column for discounts but it is not always used. Is there any
way it can recognise which cells have been used and only work out the sum
using those cells? Sorry if i dont make sense!

 

[Jarek Kujawa]

one way:

=SUM(IF(ISEMPTY(A1:A100),0,A1:A100))

this is an array-formula so CTRL+SHIFT+ENTER it instead of simply
using ENTER

 

[Ron Rosenfeld]

Is there any way of only working out a sum in the boxes that are used and
discluding the boxes that haven't been used. For example i have a worksheet
which includes a column for discounts but it is not always used. Is there any
way it can recognise which cells have been used and only work out the sum
using those cells? Sorry if i dont make sense!

If the cells "aren't being used", would they not be empty? In that case, the
SUM function will ignore them (as would the addition operator).
--ron

 

[Ron Rosenfeld]

one way:

=SUM(IF(ISEMPTY(A1:A100),0,A1:A100))

this is an array-formula so CTRL+SHIFT+ENTER it instead of simply
using ENTER

How would the result of this differ from the normally entered:

=sum(a1:a100)

???
--ron

 

[Jarek Kujawa]

I understood tha the OP only looks for the minimum non-empty cells
if there are empty cells the minimum would '0', or it wouldn't?

 

[Jarek Kujawa]

ooops, sorry
I meant to put this answer in a different thread

 

[Ron Rosenfeld]

I understood tha the OP only looks for the minimum non-empty cells
if there are empty cells the minimum would '0', or it wouldn't?

I interpreted his request as looking for the SUM of the unused cells:

"Is there any
"way it can recognize which cells have been used and only work out the sum
"using those cells?
--ron