You can't use sumif, because you only want to include the rows between the
last subtotal and the current one, i.e. you don't know what the range
reference should be. I think this will require VBA.


On Tue, 12 Oct 2004 23:41:55 -0400, "Steph" wrote:

Hi. I have no idea how to construct this formula. It could be in vb, but
doesn't have to be. Below is a database dump from a timesheet program.
What I need to do is populate the % of Total column as shown below.

For users in states OTHER than CA, we can ignore.
For CA entries, the program creates a total line for me. The data is always
sorted by user, then by state. So CA data is always lumped together per
user with the ca_total directly below the last CA entry. How can I formuate
excel to know that jblow has 2 CA entries totaling 98, and the % of the
total for each line is 64% and 36%??

Any help would be greatly appreciated! Thank you!!!

user State Total % of Total
jblow AL 28
jblow CA 63 64%
jblow CA 35 36%
jblow ca_total 98
bdole AK 18
bdole CA 38 53%
bdole CA 34 47%
bdole ca_total 72