Sum date but only if sum of date is greater than a value

[ninja1984]

I own 12 of the same asset
I rent the asset out to numerous clients on the same day on a various days in the year
I want to be able to sum the total quantity quoted to clients, but only if it exceeds 12 on each day.

01/05/17 - Quoted 10units - Sum = 0
15/05/17 - Quoted 13units - Sum = 1
17/05/17 - Quoted 20 units - Sum = 8
20/05/17 - Quoted 4 units -
20/05/17 - Quoted 6 units -
20/05/17 - Quoted 9 units - Sum = 7

Answer required = 16

Basically, what total will I have to sub hire?

[Bernie Deitrick]

An array formula, entered using Ctrl-Shift-Enter, with a formula like

=SUM((C2:C10>12)*(C2:C10-12))

Where C2:C10 contain the number of units (as a number).

A non-array formula would be

=SUMIF(C2:C10,">12")-COUNTIF(C2:C10,">12")*12

[ninja1984]

Gives an answer of 62 (sum of column C) , not the 16 required (sum of difference above 12 per day)

[leelnich]

Hi ninja- try this ARRAY FORMULA. Select any cell, paste the formula in the formula bar, then press CTRL+SHIFT+ENTER to confirm:
=SUM(IF(SUMIF($A:$A,$A:$A,$B:$B)>12,(SUMIF($A:$A,$A:$A,$B:$B)-12)/COUNTIF($A:$A,$A:$A),0))

Over12s.png

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[Bernie Deitrick]

Sorry - I misinterpreted your question.

[ninja1984]

No problem. Thank-you for replying