Formulas to CountIF? & SUM

[mrsbrannon]

Hello everyone!

I am in need of two formulas that could possibly be the same/similar. See sample tables below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Title[/TD]
[TD]Hours[/TD]
[TD]Helper Column?[/TD]
[/TR]
[TR]
[TD]June Carter Cash[/TD]
[TD]Singer[/TD]
[TD]1.33[/TD]
[TD]1.83[/TD]
[/TR]
[TR]
[TD]June Carter Cash[/TD]
[TD]Manager[/TD]
[TD]1.67[/TD]
[TD]1.67[/TD]
[/TR]
[TR]
[TD]Hank Williams[/TD]
[TD]Singer[/TD]
[TD].20[/TD]
[TD].70[/TD]
[/TR]
[TR]
[TD]Hank Williams[/TD]
[TD]Songwriter[/TD]
[TD]1.50[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]June Carter Cash[/TD]
[TD]Singer[/TD]
[TD].50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hank Williams[/TD]
[TD]Songwriter[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


My spreadsheet has many more columns than this and the data is spread throughout it, but this is just for quick use purposes. I need a formula that will do the following:

1. Find each person
2. Find their title(s)
3. SUM up the total hours worked PER TITLE, PER PERSON

I've shown the result desired in the table above.

NEXT

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Title[/TD]
[TD]Billable Amount[/TD]
[TD]Pay Out[/TD]
[TD]Profit[/TD]
[/TR]
[TR]
[TD]June Carter Cash[/TD]
[TD]Singer[/TD]
[TD]$45.00[/TD]
[TD]$120.00[/TD]
[TD]$25.00[/TD]
[/TR]
[TR]
[TD]June Carter Cash[/TD]
[TD]Manager[/TD]
[TD]$100.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hank Williams[/TD]
[TD]Singer[/TD]
[TD]$50.00[/TD]
[TD]$175.00[/TD]
[TD]$75.00[/TD]
[/TR]
[TR]
[TD]Hank Williams[/TD]
[TD]Songwriter[/TD]
[TD]$200.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need the formula to do the following:

1. Find the person
2. Find the title(s)
3. Add up the Billable Amount PER PERSON (include all titles)
4. Subtract the Pay-Out
5. Return the Profit

The concept seems to be the same as the previous formula just adding in a step or two?

All help is greatly appreciated!!

P.S. Please note, this info is on two different spreadsheets and cannot be combined.

 

[Special-K99]

Have you tried Pivot Table?

 

[mrsbrannon]

The second one is already part of a pivot table that puts out the finalized data but I'm needing to find a quicker way to add up the weekly data besides manually. It's on many sheets (one per week) so I have to add a new one each week and repeat the process.

The first one, I did try a pivot table but the table did not display in a way that made it easy to copy/paste which is what I'm doing with the output.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...cross-posted-formulas-to-countif-and-sum.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[mrsbrannon]

YES! Sorry! How do I update my ORIGINAL posts on this forum? On the other, it's super simple but I tried here and I can't figure it out. I was going to update when I cross-posted and then realized I couldn't edit my original post.

 

[Fluff]

As the link is now on the thread you don't need to do anything.
On this site there is a maximum of 10 minutes to edit your post (depending on how many posts you've made).
In future simply hit the reply button & supply the link that way.
Thanks

 

[mrsbrannon]

Will do, thank you.

And I'll add - SOLVED