Help with a formula

New Contributor

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!

Randimarie143_0-1649740615564.png

 

6 Replies

Hi @Randimarie143 

 

if I understood everything correctly, you could you a SUMIFS formula:

DTE_0-1649778531080.png

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

 

@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?

 

Randimarie143_2-1649829322395.png

 

Thank you for helping me with this!

Hi @Randimarie143 

 

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...

@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!!!

 

Randimarie143_0-1650302594122.png

 

Hi @Randimarie143 

 

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")

Oh geez! I'm sorry! I'll go back and try it again. Maybe I plugged in the wrong column the first time. I'm sorry to make you scratch your head so much on this. LOL. Thank you for your help! It is so much appreciated!!