HELP! In desperate need of a formula

[Joanna]

I'm working on a settlement statement that is 112 pages. Column G=the
attorneys' name and Column F=rates for the different attorneys. Different
attorneys have different rates. At the end of my statement, I have a section
that needs to tally each attorey's fee for a total. I can't come up with a
formula that will calculate this. Please help! I'm up against a hard
deadline! Thanks in advance!

 

[Marcelo]

try a pivot table.


--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Joanna" escreveu:

 

[Eric]

Somewhere along the line there has to be a number of hours to apply to the
rate?

And I'm assuming that each attorney has more than one line charge, otherwise
you could do a sort of the one line for each attorney to get your data in a
logical order without formulas.

In any event, a SUMIF function is probably your answer.

A simple example:

Col A Col B Col C Col D
1 Name Rate Hours Total
2 John $100 50 $5,000
3 Dave $50 20 $1,000
4 Dave $50 40 $2,000
5 John $100 20 $2,000
6 Total 130 $10,000

Now, below that you employ your SUMIF summaries to tie to the $10,000 total
to wind up with:

8 Dave 60 $3,000
9 John 70 $7,000
10 Total 130 $10,000

Formula in C8: =SUMIF($A2:$A5,$A8,C2:C5)
Formula in D8: =SUMIF($A2:$A5,$A8,D25)

Copy C&D down to Row 9 to replicate.

Note that the spellings of "Dave" and "John" in A8 and A9 must be an exact
match to the way they're spelled in A2:A5 for this to capture the line items
properly, and you need to account for every attorney in the summary section.
Check that you captured everything in the summary by doing a total on both
sections.

Good luck!

 

[Eric]

Marcelo,

See my answer please--is there an easier solution if you need to show both
hours and total dollars for each attorney?

A pivot table could supply either hours or dollars, but not both, correct?

This may not apply to the OP, but it is something I deal with fairly often
and am always looking for a better way.

Thanks.

 

[Joanna]

Thanks SOOOO much, Eric! That is exactly what I need....I think. I'll try
this and see what happens! Thanks for all your help! I'll let you know how it
goes!

Joanna

 

[Joanna]

Thanks, Marcelo. Not sure what a pivot table is, but I'll check it out!

 

[Eric]

Formula in C8: =SUMIF($A2:$A5,$A8,C2:C5)

Formula in D8: =SUMIF($A2:$A5,$A8,D25)

C2:C5 and D25 should also have a dollar sign in front of the 2's and 5's.
Otherwise when you copy down, it'll become C3:C6, etc.

If you only need to sum up one data element (hours or total dollars), you'll
definitely want to do the pivot table.

And I'd do the pivot table just to make sure you have all your attorneys
names covered that appear in the invoice. You can copy and paste just the
column with the attorney names as the basis for your SUMIF summary and know
that you have all the names and that they're spelled exactly the same as in
the detail with 100% confidence. Just ignore the other data the pivot table
returns and then you can delete the pivot table after you've pulled out the
names.

Translation: Take a minute NOW (even thought you're incredibly busy and on
deadline) to learn what a pivot table is. You will get that minute back at
least 20 times over in preparing this current invoice, and it will be
invaluable knowledge many times in the future.

 

[Joanna]

Eric....You are my hero!!!! I tweaked the formula a little to reflect (from
your example): =SUMIF(A2:A5,"John",B2:B5) I'm off about $600.00 but with all
the attorneys and staff, I may have left off someone! You have made my DAY!!!
Thank you!

 

[Joanna]

Gotcha! That's where my $602.00 went! I changed the formula to say:
=SUMIF($A$2:$A$5,"John",$D$2:$D$5) and it worked perfectly....Seriously...How
does it feel to be someone's hero tonight??? I can't thank you enough!