subtotal using sumif

[kelseyalyce]

I want to be able to use a sumif function, and make subtotals of each of my employees names. My problem is that when I use the sumif function, it works until I use the excel subtotal option. It then adds the subtotals into the sumif fuction. I guess what I'm wondering is if there is a way to use something that is essentially a "subtotalif"

Thank you.

2012-06-22 through 2012-06-24.xls

[martindwilson]

could you explain,especially why you include the sub total in the sum if range?

[kelseyalyce]

I don't. It includes it automatically. I used [in C100]: SUMIF=(E2:E99, ">0", C2:C99) [in D100]: SUMIF=(E2:E99, ">0", D2:D99) and [in E100]: SUMIF=(E2:E99, ">0") and when I go to Data>Subtotals, I use these options: At Each change in: Name, Use function: Sum, Add subtotal to: time. target. o/u target.
The "Grand Total (not incl under targets)" row then changes and adds the subtotals of each employee that are >0 as well as all of the numbers in those columns that are >0.
I want to be able to stop it from doing that. I need the grand total that do not include the under targets for each day, AS WELL AS the subtotals for time, target, and o/u target of all of the staff.

[Banuchander]

Hi, I think the attached excel will help you..

[kelseyalyce]

Hi, I think the attached excel will help you..

I'm not exactly sure how this is helpful? Now you're only adding E2:E23. How does that give me the subtotals of every employee along with the grand total > 0?

You didn't change anything. You just changed the sumif to have less cells. The C column and D column are exactly the same and doing the thing I am having issues with.

Thanks for the help.

[martindwilson]

use a pivot table

[oeldere]

@kelseyalyse

banuchander added in #4 an pivot table in sheet 4.

And that's a solution I should adviced you also.

[kelseyalyce]

how do you use a pivot table?

[oeldere]

to make an pivot table.

Excel 2007 => add table => include headers

Add pivot table.

It can be add on the same worksheet or if you have a large table on a diffent sheet.

You drag en drop the wanted headers in the pivot table.

Is this explained well?

Do you have a question on this item, just ask.

[kelseyalyce]

I don't know what pivot tables are for.

Thanks, though.

[oeldere]

look in sheet 4 of the added example in #4.

There you see a pivot table.

There you see what a pivot table could do for you.

Do you have a question on this item, just ask.

[kelseyalyce]

Looking at it does not explain what it's doing.

[Banuchander]

Actually the cell 124 was hided in the sheet after i have done some changes in the sheet, now the attached has Grand Total as well as Grand Total (not incl under target) and the subtotals too.
I hope this will help you.

[kelseyalyce]

Actually the cell 124 was hided in the sheet after i have done some changes in the sheet, now the attached has Grand Total as well as Grand Total (not incl under target) and the subtotals too.
I hope this will help you.

It's still incorrect. The Grand Total (not incl under target) is still way too high.

[oeldere]

Please Login or Register  to view this content.

in the sheet 4 it sums the time - target - o/u target per name (on sheet 1)

e.g. Antonio, Acres

If you give the result in your sheet, i provide an example with a pivot table for you.