Hi everyone,

I am a voluntary treasurer of a local charity and I am building an excel model to capture members subscriptions and their additional donation.

The model I have has three tabs; Payments, Subscriptions and donations. Members who are singles contribute £5 a month and couples contribute £10, but sometimes members contribute less than their monthly subscriptions. However, sometimes members pay their subscriptions together with donations. I want a formula in the subscriptions tab that is able to split the subscription amount from donation and ensure that the amount for a couple or single is entered even if the amount paid is less than the required subscription.

E.g. a member who is single paid £35 in a month. This is entered against the member in the payments tab. I want a formula in the subscriptions tab to take only £5 as subscriptions against that member and the donations tab to take the rest. Where a member pays less than the required subscription, the full amount is entered as subscriptions.

I have tried and cannot figure out what formula I need. Below is the formula I put in Subscriptions tab but is giving me £5 even if the subscription is paid by a couple.

=IF(AND(E6="Single",'2018 Payments'!G6<=5),'2018 Payments'!G6,IF('2018 Payments'!G6>5,5,IF(AND(E6="",'2018 Payments'!G6<=5),'2018 Payments'!G6,IF('2018 Payments'!G6>5,5,IF(AND(E6="couple",'2018 Payments'!G6<=10),'2018 Payments'!G6,IF('2018 Payments'!G6>10,5,0))))))

Where column E identifies the member as being single or couple


Your help in this will be greatly appreciated.

Regards

Lome