Subtotals based on criteria 1

[newguy86]

I have a report that lists the number of hours a resource is allocated for each project they are on. Currently the report shows a subtotal for the number of hours in a given month which updates itself everytime someone filters the data. What they now want is another subtotals field to add together the percentages of a resources allocation and have that total update everytime they filter the data.

Now here's where it gets tricky...the percentage of allocation is based on the total number of hours available for a given month and varies depending on any holidays that are in that month. Additionally some resources are offshore and some resources are US based which means that each month could result in a different number of hours available for those two types of resources.

So far I'm able to get a subtotal for all hours listed in a month collectively which calculates based only on the visible data and I can get a total number of hours for all resources (regardless of whether or not they're visible) in the report split out between the two types and then converting that number to a total of allocation percentages but the problem I'm having is that the later formula keeps spitting out the same number regardless of how I filter the data.

Any ideas on how I can accomplish this?

Here's the formula that I'm using to get a subtotal of the hours in a given month:

=SUBTOTAL(9,M13:M2605)

Here's the formula that I'm using to get the total number of hours for all resources and then converting that number to a total of allocation percentages:

=SUM((SUMIF($G13:$G1048576,"<>Offshore",M13:M1048576)/M3)+(SUMIF($G13:$G1048576,"Offshore",M13:M1048576)/M4))

Here's what the data looks like (or at least the important bits):

Column G (Type)...Column M (Apr-13)...Column N (May-13)
US Based..............88.00......................88.00
US Based..............56.00......................44.00
US Based..............32.00......................44.00
Offshore..............176.00.....................184.00
Offshore..............176.00.....................184.00

Finally to clarify some loose ends, the data starts on row 13 and the total number of hours available in a given month for US Based and Offshore resources are stored in cells M3 & M4 respectively for Apr-13 and N3 & N4 for May-13 and their values are as follows:

Type..........Apr-13.....May-13
US Based...176.00.....176.00
Offshore....176.00.....184.00

If you have any other questions feel free to let me know and any help you can provide will be greatly appreciated.

Travis

http://www.apexsystemsinc.com

 

[SkipVought]

hi,

As long as you have data in your table that GROUPS the rows that belong to each subtotal group, its either a SUMIFS() function or a SUMPRODUCT() function.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newguy86]

I'm not sure I'm understanding where you're coming from. I can get the SUMIFS() function to work but I still have the same issue as before which is that when someone filters the data the formula result doesn't change to reflect the totals of only the visible data. It just keeps giving me the total for all records in the spreadsheet. And I'm not seeing how I can use the SUMPRODUCT() function because according to the help files the function will just multiply the results of each array together which isn't what I need.

What I'm looking for is a function that will basically look through the visible data in the designated column and add up the values that meet the criteria.

Here's another way of looking at it...if you look at the example in my last post, my current functions are returning the following numbers:

528 (Hours Subtotal) (Using the SUBTOTAL() function)
3 (Allocation % Total) (Using the SUM() function)

Now if I filter the data to only include the first "US Based" record and the first "Offshore" record in the list my expected result should be this:

264 (Hours Subtotal)
1.5 (Allocation % Total)

But what's actually returning is this:

264 (Hours Subtotal) (Using the SUBTOTAL() function)
3 (Allocation % Total) (Using the SUM() function)

In other words the SUBTOTAL() function is evaluating the data the way I need the SUM() function to but I cannot figure out how to add criteria to the SUBTOTAL() function so that I can separate the values by type (Like I did in the SUM() function above) and then finish converting those values into allocation percentage totals.

So is that possible or is there a better way to accomplish that?

Travis

http://www.apexsystemsinc.com

 

[byundt]

You can use SUBTOTAL in a SUMPRODUCT formula if you wrap it in OFFSET:
=SUMPRODUCT(((G13:G1048576="Offshore")/M4+(G13:G1048576<>"Offshore")/M3)*(SUBTOTAL(109,OFFSET(M13,ROW(M13:M1048576)-ROW(M13),0,1,1))))

SUBTOTAL(109, single cell range) returns only visible values, so you just need a Boolean expression to test for Offshore in the SUMPRODUCT

This formula takes a couple of seconds to calculate on my Mac. I suggest that you reduce the number of rows being analyzed to those that have a reasonable chance of being used. Instead of 1048576, use 1000 (or any other number that will include all reasonably possible data). FWIW, 100,000 rows calculated almost instantly--but 1,000,000 rows took 1 to 2 seconds.

Brad

 

[newguy86]

Sorry for not responding back earlier. The formula worked perfectly. But now they want to add the "Onsite Offshore" type to the Offshore calculation in order to use that reference.

I have updated the formula but I seem to be getting twice the amount I'm expecting.

Here's my formula:

=SUMPRODUCT((($G13:$G5000="Offshore")/M4+($G13:$G5000="Onsite Offshore")/M4+($G13:$G5000<>"Offshore")/M3+($G13:$G5000<>"Onsite Offshore")/M3)*(SUBTOTAL(109,OFFSET(M13,ROW(M13:M5000)-ROW(M13),0,1,1))))

What am I doing wrong?

Travis

http://www.apexsystemsinc.com

 

[byundt]

Your problem is with <>"Offshore" and <>"Onsite Offshore". I suggest you list the choices that might appear instead of Offshore and Onsite Offshore instead of using <>.

 

[newguy86]

I figured that's where my problem was but I thought there was a way to get the sum of a range where the value of the corresponding cell is not equal to "Offshore" AND is not equal to "Onsite Offshore". Am I wrong about that?

Travis

http://www.apexsystemsinc.com

 

[byundt]

<>"Offshore" includes "Onsite Offshore"
<>"Onsite Offshore" includes "Offshore"

You could subtract the parts that don't belong from the part of formula dividing by M3.

=SUMPRODUCT((($G13:$G5000="Offshore")/M4+($G13:$G5000="Onsite Offshore")/M4+($G13:$G5000<>"Offshore")/M3-($G13:$G5000="Onsite Offshore")/M3)*(SUBTOTAL(109,OFFSET(M13,ROW(M13:M5000)-ROW(M13),0,1,1))))

 

[newguy86]

That works...thank you!

Travis

http://www.apexsystemsinc.com