# IfF/AND formula for a range of cells

#### Lioness227

##### New Member
Hi,

I am confused as to which formula would be more appropriate for the below problem I am trying to resolve.

2020 monthly payment tab
I have a list of participants who give £3 per month and can select a product. They can stop participating at any time.
If product A, £1 is allocated to product A budget. And 1 cent is allocated to 53 other products budgets (1 cent each).
Same rule apply to each product.

2020 one off payment tab
I have a list of participants who give whatever they want, for example £100 in December, as a one off.
1% of the £100 will be allocated to each product budget on the month the £100 have been given, so in this case £1 each in December.

last tab where formulating each product budget I have for each month.

=if(and(‘2021-Monthly payment’!C3:C1000=“Product A”,’2020-Monthly payment”!D3:D:1000=3), A60*sum(‘2020-Monthly payment”!D3:D1000), A59*sum(‘2020-Monthly payment”!D3:D1000))+(B62*’2020-one off payment’!C2:C1000)

Data
A59= 0.0033333333
A60=0.33333334
B62=0.01

C3:C1000 is the column where Product name is written
D3:D1000 is the range of monthly participation’s £3 on each line

C2:C1000 is the range of one off payment

QUESTION
Shall I actually use another formula and I am completely wrong using if/and formula? Shall I look at pivot table? I can’t think anymore, been on this one for too long to find au automatic solution rather than doing it manually!

Many thanks in advance (and thankfully it is clear enough, if not please let me know).

#### Attachments

• 33B95DBB-BDAA-48FF-977E-D8D24370C7F3.jpeg
91.6 KB · Views: 3
• 1658984C-6A76-4AC7-97BA-CB9555069C3D.jpeg
103.6 KB · Views: 3

#### Lioness227

##### New Member
Hi,

i will stop chasing you and find someone else to help no worries, I understand you are busy.

Many thanks,

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Replies
3
Views
601
Replies
0
Views
131
Replies
0
Views
1K
Replies
1
Views
411
Replies
0
Views
408

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,589
Messages
5,765,311
Members
425,272
Latest member
Umba

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back