Excel - Functions stemming from data validation list selection

Question

I am creating a worksheet to track charity spend/fundraising throughout the year. Money raised will support x2 charities, and most of what we raise gets divided between the two unless supported directly (or also split between a third charity if this is requested).

I have created a data validation list in column C, to choose whether the money raised goes to Charity A, Charity B, Both, or A B & C. I then have the total raised in column I, and would like to see if I can create a function to do the below:

Column J (Charity A total)	If Charity A is selected, cell = total raised
If Charity B is selected, cell = 0
If Both is selected, cell = total raised /2
If Charity A B & C is selected, cell = total raised /3
Column J (Charity B total)	If Charity A is selected, cell = 0
If Charity B is selected, cell = total raised
If Both is selected, cell = total raised /2
If Charity A B & C is selected, cell = total raised /3
Column L (A B & C total)	If Charity A is selected, cell = 0
If Charity B is selected, cell = 0
If Both is selected, cell = 0
If Charity A B & C is selected, cell = total raised /3

Trying to cut out the need for making the calculation myself (being lazy if anything) but if you have any suggestions that would be amazing! If this will just result in a ridiculous formula that is more effort that doing the calculations, then that is fine, I am not the most technical.

Answer 1

Hi Jessaahh4,

Based on the information you provided, I have created a simple example for you with the IFS function. Below is the table I created based on your description:

In column F, G & H is the total of Charity A, Charity B and Charity A B & C, the formulas are the below:

Charity A = IFS(C1=B1,SUM(E2:E7),C1=B2,"0",C1=B3,SUM(E2:E7)/2,C1=B4,SUM(E2:E7)/3)

Charity B = IFS(C1=B1,"0",C1=B2,SUM(E2:E7),C1=B3,SUM(E2:E7)/2,C1=B4,SUM(E2:E7)/3)

Charity A B & C = IFS(C1=B1,"0",C1=B2,"0",C1=B3,"0",C1=B4,SUM(E2:E7)/3)

If there is any misunderstood, please feel free to provide more information to us.

Regards,

Tisky

Answer 2

Tisky - this is amazing! Thank you. I am no genius on excel but managed to figure out how to alter these to suit where certain things are in my table and the data for the list on another sheet, and it works! 

I cannot thank you enough for taking the time to create this response!