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:

Name	Title	Hours	Helper Column?
June Carter Cash	Singer	1.33	1.83
June Carter Cash	Manager	1.67	1.67
Hank Williams	Singer	.20	.70
Hank Williams	Songwriter	1.50	4
June Carter Cash	Singer	.50
Hank Williams	Songwriter	2.5

<tbody>
</tbody>

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

Name	Title	Billable Amount	Pay Out	Profit
June Carter Cash	Singer	$45.00	$120.00	$25.00
June Carter Cash	Manager	$100.00
Hank Williams	Singer	$50.00	$175.00	$75.00
Hank Williams	Songwriter	$200.00

<tbody>
</tbody>

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