Sum the surplus

**yogeshmaney** · 09-05-2009, 06:35 AM

Hi,

I want to Sum up the difference between base value= 5 and cell value

if the table is like

A2:2
A3:6
A4:7

I want the summation of excess than base value=5
which is equal to 3
Regards,

---Yogi

**DonkeyOte** · 09-05-2009, 06:47 AM

I don't understand how you get 3 from your result of 15 with base of 5 - surely it's 0 - ie:

=MOD(SUM(A2:A4),5)

Ah, hang on maybe you mean

=SUMPRODUCT(--(A2:A4>5),A2:A4-5)

or even

=SUMIF(A2:A4,">5")-5*COUNTIF(A2:A4,">5")

**martindwilson** · 09-05-2009, 06:49 AM

7+6+2 =15
surplus after dividing by 5 is 0
3 lots of 5 no surplus
what do you mean?

**yogeshmaney** · 09-05-2009, 07:00 AM

what if I need to count the excess time spend instead of just numbers

**yogeshmaney** · 09-05-2009, 07:08 AM

what if I need to count the excess time spend instead of just numbers

**yogeshmaney** · 09-05-2009, 07:44 AM

Hey Guys Kindly check the following,
is this ok

---Yogi

**martindwilson** · 09-05-2009, 08:26 AM

you can get the same result
with
=SUMIF(B4:B6,">"&B1,B4:B6)-COUNTIF(B4:B6,">"&B1)*B1

**DonkeyOte** · 09-05-2009, 08:38 AM

Hi Martin, in this instance there is no need to set the optional sum_range in the SUMIF given the intial range contains values to SUM, eg:

=SUMIF(B4:B6,">"&B1)-COUNTIF(B4:B6,">"&B1)*B1

(resulting cell set to format of h:mm:ss)