[psmithy]

Hello team!

My problem is similar to this post:
http://forums.techguy.org/business-applications/761733-solved-ignore-rows-certain-text.html

I have been working in a documet in Excel 2007 and I need to add up peoples hours according to two task catergories; Core & Non Core (Columns H & I). The hours are recorded in G.

Here are the formulas I'm using:
{=SUMIF(H1:H20721,"<>"&"",G1:G20721)} and {=SUMIF(I1:I20721,"<>"&"",G1:G20721)}

So if "H" or "I" is blank, don't use the hours in G for calculation. It works almost perfectly The only problem I am having is that I need to filter my info according to specific users.

This means, I have 30 users all with tasks in the data. If I filter on User A, that user will have a combination of Core Tasks and Non Core Tasks -

F G H I
(USER) (HOURS) (CORE) (NON CORE)
1 A 1.0 Scheduling
2 A 2.0 Leadership
3 A 1.5 Scheduling
4 A 0.5 Content
7 A 2.3 Email
10 A 0.7 Compliance
12 A 1.5 Meeting
13 A 1.0 Email
20 A 2.5 OHS
21 A 1.3 Meeting

I should get the following responses:
TOTAL = 14.3 {=SUBTOTAL(9,G1:G20721)}
TOTAL CORE (H) = 5 {=SUMIF(H1:H20721,"<>"&"",G1:G20721)}
TOTAL NON CORE (I) = 9.3 {=SUMIF(I1:I20721,"<>"&"",G1:G20721)}

When I apply my User filter, the formulas return a total value of the entire G Column (hours) instead of the hours pertaining to the user, i.e. they're all users ignoring the filter.

This is my formula for total hours {=SUBTOTAL(9,G1:G20721)} and this works perfectly for me when I apply filters.

Does anyone know a way to overcome my problem?

Appreciate any help you can give me

[Jubbaloo]

Could you use PIvot Tables, this would allow you to filter data and still work with sophisicated Sums and counts if needed

[psmithy]

Thanks - I worked it out. It was a long formula....SUMIFS worked.
Solving