PDA

View Full Version : [SOLVED] Summing individual rows, for overall group total



ashleyuk1984
11-23-2017, 04:32 AM
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.

21031

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

Thanks

paulked
11-23-2017, 11:27 AM
I just did a sumif and hid the columns you don't want to see on the report.

21033

Cheers

Paul Ked

ashleyuk1984
11-23-2017, 01:46 PM
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
11-23-2017, 01:57 PM
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
11-23-2017, 02:21 PM
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
11-23-2017, 02:22 PM
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
11-23-2017, 03:18 PM
Thank you very much paulked and p45cal.
Both of those worked perfectly!

paulked
11-25-2017, 03:31 AM
Please mark as Solved :)