Formula Help

[mahmed1]

Hi Guys

I have a range n2 - 49 where i should enter the value of £50. However there are some instances where i am entering less than £50. Some are left blank as they have not been filled in yet.

e.g

50
50
35
20
20
35
50
50

What i need to sum up is the difference between the values which have payed less than 50.

i.e 35 is 15 less than 50
20 is 30 less than 50
20 is 30 less than 50
35 is 15 less than 50

15
30
30
15

£90

Count cells if not blank

Thanks

 

[T. Valko]

Hi Guys

I have a range n2 - 49 where i should enter the value of £50. However there are some instances where i am entering less than £50. Some are left blank as they have not been filled in yet.

e.g

50
50
35
20
20
35
50
50

What i need to sum up is the difference between the values which have payed less than 50.

i.e 35 is 15 less than 50
20 is 30 less than 50
20 is 30 less than 50
35 is 15 less than 50

15
30
30
15

£90

Count cells if not blank

Thanks

Try this...

=SUMPRODUCT(--(N2:N49<>""),50-N2:N49)

 

[dfenton21]

Hi there,

This formula should work for you:

=SUM(50-N2:N49)

It is an array formula, so you'll need to enter it by pressing SHIFT + CTRL.

Let me know if you have any further queries.

 

[mahmed1]

Thank You all, i'm a bit tired, don't why i did not think of that. I think il just call it a night

Thank You

 

[T. Valko]

Thank You all, i'm a bit tired, don't why i did not think of that. I think il just call it a night

Thank You

You're welcome. Thanks for the feedback!