Summing individual rows, for overall group total

[ashleyuk1984]

Hi,
My title might not be very clear, but I'll try to explain as best as I can, what I require help with.

I run a weekly report and on this report are a number of chargeable items, but the number of chargeable items only start if the value is over 3..

So, if the items only go up to 2 on that paticaluar line, then the number of chargeable items is 0.

1 = 0
2 = 0
3 = 0
4 = 1 (because we're now into charging territory) (it's basically 4 - 3 = 1)
5 = 2 (because we're now into charging territory) (it's basically 5 - 3 = 2)
6 = 3 (because we're now into charging territory) (it's basically 6 - 3 = 3)

It gets tricky, because these rows are grouped together by a "Master Reference"... But I need to calculate each row separately to get the true figure.

screenshot.jpg

I've attached an example spreadsheet to show my problem.
I hope you can help.

Thanks

[paulked]

I just did a sumif and hid the columns you don't want to see on the report.

grouping problem ked.xlsx

Cheers

Paul Ked

[ashleyuk1984]

Hi Paulked,
Yeah that's the easy way to do it... But I don't want those helper columns on my report - at all.
If there's no other way of doing it, then I'll have to resort to that method, but I'd rather have some sort of array formula that does all the work without the aid of helper columns.

Thanks

[paulked]

Hi Ash,

I thought you might, I hate working with arrays!

I would do it in VBA but could you get away with doing the calculations on a hidden sheet?

Cheers

Paul Ked

[paulked]

Hi Ash,

Try =SUMIFS(B:B,A:A,B19,B:B,">3")-(COUNTIFS(A:A,B19,B:B,">3")*3)

Works for me!!

Cheers

Paul Ked

[p45cal]

Array-enter (Ctrl+Shift+Enter instead of just Enter) into B20:
=SUM(IF(B4:B15-3>0,B4:B15-3,0)*(A4:A15=B19))

[ashleyuk1984]

Thank you very much paulked and p45cal.
Both of those worked perfectly!

[paulked]

Please mark as Solved