Apr 11 2022 10:17 PM
Hello! I am trying to find out a formula that will separate 2 people's pay. For instance, in this spreadsheet, Randi is paying Rena on some of the deals. I'd like to know how much Randi is getting on the deals that Rena is doing both "pending" and "sold". I hope that makes sense!
Apr 12 2022 08:49 AM
if I understood everything correctly, you could you a SUMIFS formula:
So in my example:
=SUMIFS(C2:C7;B2:B7;"Randi";A2:A7;"Pending")
Note: Depending on your regional settings you might need to replace ; with , in the formula
Apr 12 2022 10:56 PM
@Martin_WeissThank you so much for the reply! I think you're super close! In my example, I have a line that says "paid to Rena". She's an employee of mine and I get a small portion of her commission. On my files, I keep all the commission. I'm trying to find out how much I will get paid on her pending files and my files as well as how much I got paid on her closed files and my files. So in the example below, on the sold status', Randi made $400 on her own files and $100 off of Rena's files. Those have been closed and paid. On the pending files, Randi made $150 on her own files and $250 on Rena's files.
What formula would I need to automatically calculate that once the status is changed?
Thank you for helping me with this!
Apr 14 2022 04:23 AM
I'm getting confused now about the figures that you mention. What are the exact criteria and which colums need to be summed up?
Is it column "Total after"?
And how comes the column "Paid to Rena" into play?
Are the criteria always the combination of column "Status" and column "TC". E.g.
Sum up all "Total after" for combination of Sold and Randi
Sum up all "Total after" for combination of Pending and Randi
Sum up all "Total after" for combination of Sold and Rena
Sum up all "Total after" for combination of Pending and Rena?
Is it that what you need?
Sorry, but I have now more questions than answers...
Apr 18 2022 10:26 AM
@Martin_Weiss sorry for the delay!
Okay so the total after is what I'd like to know. That amount is the amount that I get paid. I'd like to break it down to see what is "pending" to be paid and what was already paid (sold).
The "paid to Rena" doesn't apply:) Sorry for the confusion.
You are correct on your assumption for what I am looking for:
Sum up all "Total after" for combination of Sold and Randi
Sum up all "Total after" for combination of Pending and Randi
Sum up all "Total after" for combination of Sold and Rena
Sum up all "Total after" for combination of Pending and Rena
Thank you for your help!!!
Apr 19 2022 06:09 AM
but that's exactly what my formulas in my first answer would do.
You just need to adjust it to the correct columns in your file and you do it also for the other two combinations.
So, applied to your screenshot, the formulas would be:
=SUMIFS(H2:H7;B2:B7;"Randi";A2:A7;"Pending")
=SUMIFS(H2:H7;B2:B7;"Randi";A2:A7;"Sold")
=SUMIFS(H2:H7;B2:B7;"Rena";A2:A7;"Pending")
=SUMIFS(H2:H7;B2:B7;"Rena";A2:A7;"Sold")
Apr 19 2022 11:00 AM