Calculating totals based on values of another cell-CodePudding

How would I calculate totals from cells based on the content of another cell:

I will have 3 selectable options in a data validation, 2 People, and one as "Split" and would like to calculate what each person has spent, the overall total and % breakdown.

Sometimes the two people split the cost of an item 50/50 by selecting the Split option

I need help with two things:

How to SUM based on which person is selected
How to split an amount when 50/50 and add to their totals when Split is selected

example below:

CodePudding user response：

Your formula should look like

=SUMIF(A5:A10,"Bob") (SUMIF(A5:A10,"Split")/2)

Next one

=SUMIF(A5:A10,"Jean") (SUMIF(A5:A10,"Split")/2)

That should work

Hope it helps!

CodePudding user response：

use:

={SUM(B6:B); INDEX(QUERY(SPLIT(TRIM(FLATTEN(SPLIT(QUERY(IF(C6:C="split", 
 {"♦"&B6:B/2&"♥bob♦"&B6:B/2&"♥jean"}, 
 IF(C6:C="",,"♦"&B6:B&"♥"&C6:C)),,9^9), "♦"))), "♥"),
 "select sum(Col1) group by Col2 label sum(Col1)''"))}