Modification to SUMIFS INDEX - Help please

[reevesgetsaround]

Hi everyone

I am hoping if someone could help/advise with this fiddly problem I'm having on the attached. I have a formula in Sheet2 which is very effectively tracking fictitious donations based on selections made in Sheet1. My only issue with this is that if you look cells C4 and C10, it duplicates the 'donation' amount. This could potentially skew my data and vastly overestimate.

My question is; is it possible that this formula could be modified so that it splits the donation amount based on the number of 'charities' chosen per row? I.e. cells C4 and C10 would become 2500?

And if not, any alternative suggestions would be warmly welcomed.

Many Thanks 

[#2]

@reevesgetsaround

If it's OK to make the amounts in Sheet1 column D unique, you can use the formula in the attached version. It divides the donation amount by the number of charities that contributed that amount.

Having 2000 occur twice caused problems with that. 

[#3]

Thank You Hans, I could perhaps use this approach as a workaround. However I have noticed that when a new donation value is added under the same column, the figure reverts to zero.

[#4]

@reevesgetsaround 

Please provide an example.

[#5]

So if a new donation figure is added in column D and a charity selected which already has a checkmark under it, Charity 1 for example, the figure in the Sheet2 formula corresponding the Charity 1 now reads as 0 for me.

Appreciate your help.

[#6 · Accepted solution]

@reevesgetsaround 

Yeah, OK, that complicates things (and that's why it's always better to provide a realistic example at the outset instead of adding complexity step by step).

New version attached. I added a helper column to Sheet1.

[#7]

Thanks so much Hans. This is really helpful.