# 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: 6
• 1658984C-6A76-4AC7-97BA-CB9555069C3D.jpeg
103.6 KB · Views: 6

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### jtakw

##### Well-known Member
Hi,

Looking at the formula you're using (or trying to use), I don't think it'll work, you have made arrays inside the AND statement, but it won't work as-is, even if the formula is array entered (CSE).
I can't make any suggestions at this time, as you only uploaded pics/screenshots, it's nearly impossible for me (or any other potential helper) to reconstruct your sheets for testing.

Please check my signature and upload Sample sheets using XL2BB, you'll find that members will be much more inclined to help.

#### Lioness227

##### New Member
Hi,

Looking at the formula you're using (or trying to use), I don't think it'll work, you have made arrays inside the AND statement, but it won't work as-is, even if the formula is array entered (CSE).
I can't make any suggestions at this time, as you only uploaded pics/screenshots, it's nearly impossible for me (or any other potential helper) to reconstruct your sheets for testing.

Please check my signature and upload Sample sheets using XL2BB, you'll find that members will be much more inclined to help.
Hi,

Thank you for your response. I should have uploading via XL2BB, thankfully it worked and you can see?

ABCDEFGHIJKLMNO
1
2NameSurnameProductJan 2020Feb 2020Mar 2020Apr 2020May 2020Jun 2020Jul 2020Aug 2020Sep 2020Oct 2020Nov 2020Dec 2020
3LolaProduct A33
4FredProduct B33
5GeorgeProduct C33
6DavidProduct D33
7LiamProduct A33
8MarkProduct D33
9
10
11
2020-Monthly payment

ABCDEFGHIJKLMN
1NameSurnameJanuary 2020February 2020March 2020April 2020May 2020June 2020July 2020August 2020September 2020October 2020November 2020December 2020
2Lisa100
3Laurent300
4Jessica400
5Oliver100
6Olivia200
7Manuel1000
8Dario5000
9Lou200
2020-One off payment

ABCDEFGHIJKLMN
1ProductIDJan Fund RaisedFeb Fund RaisedMar Fund RaisedApr Fund RaisedMay Fund RaisedJun Fund RaisedJul Fund RaisedAug Fund RaisedSep Fund RaisedOct Fund RaisedNov Fund RaisedDec Fund Raised
2Product A#VALUE!
3Product B
4Product C
5Product D
6Product E
7Product F
8Product G
9Product H
10Product I
11Product J
12Product K
13Product L
14Product M
15Product N
16Product O
17Product P
18Product Q
19Product R
20Product S
21Product T
22Product U
23Product V
24Product W
25Product X
26Product Y
27Product Z
28Etc.
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57TOTAL
58
590.0033333330.01
600.333333341
61Monthly3
62One off:0.01
63
2020-budget
Cell Formulas
RangeFormula
C2C2=IF(AND('2020-Monthly payment'!C3:C1000="Product A",'2020-Monthly payment'!D3:D1000=3),\$A\$60*SUM('2020-Monthly payment'!D3:D1000),\$A\$59*SUM('2020-Monthly payment'!D3:D1000))+(\$B\$62*'2020-One off payment'!C2:C1000)

#### jtakw

##### Well-known Member

In the formula you're trying to use, you have 2 different calculations.
I already have a formula for the first part; however, I'm not understanding your requirements for when and which calculation to use.

Can you please explain in detail what you expect as the answer (and why) for Budget sheet C2, and is the formula supposed to produce results across for the Months, or down for the Products?
Also, please confirm that the values in Budget sheet A59:B62 are static.

#### Lioness227

##### New Member

Hi,

thank you very much for your help. Apologies a the notification from MrExcel went to my spam.

i am looking for
- Budget raised from monthly payment per month and for each product (depending on which product customers select, and when, the result will differ, as they can start and stop at any time so we cannot allocate automatically a 12 month subscription = £36)
- Budget raised from the one off payment (can come from any existing or new customer, and whatever the amount, 1% will be allocated to each product). There are 54 products in total for now so I need to extract 54% of that amount to be allocated equally to each product, and the rest so 46% will be allocated to something else.

does that make sense?

values in budget sheet A59:B62 are static indeed.

#### Lioness227

##### New Member
Hi,

thank you very much for your help. Apologies a the notification from MrExcel went to my spam.

i am looking for
- Budget raised from monthly payment per month and for each product (depending on which product customers select, and when, the result will differ, as they can start and stop at any time so we cannot allocate automatically a 12 month subscription = £36)
- Budget raised from the one off payment (can come from any existing or new customer, and whatever the amount, 1% will be allocated to each product). There are 54 products in total for now so I need to extract 54% of that amount to be allocated equally to each product, and the rest so 46% will be allocated to something else.

does that make sense?

values in budget sheet A59:B62 are static indeed.

@jtakw hopefully you saw my response here

#### Lioness227

##### New Member

@jtakw hope you can still help

#### jtakw

##### Well-known Member
Apologies for not getting back to you sooner, been real busy with work past couple of weeks, and still am, was only able to log in now and then for a short time.

I did see your response in Post #5, however, I'm still not clear what your desired outcome is.
As I asked, I see that you have 2 Different calculations in your formula, I'm unclear as to what condition triggers which calculation.

For the 1st part of your formula, this is what I have:

Excel Formula:
``=SUMPRODUCT(('2020-Monthly payment'!\$C\$3:\$C\$1000=\$A2)*('2020-Monthly payment'!D\$3:D\$1000=3)*\$A\$60)``

I don't know if we're supposed to Add the second part of your formula to this result, OR, under some condition, it's the result from above, otherwise, use the second part...?
Your 2020-One off payment sheet has no product codes, so for 1/1/2020, we have 3 from various people for various products in 2020-Monthly payment, AND, 100 from 1 person not assigned to any product, I see you say 1% to each of 54 products (and where does the rest go?), but as I asked above, do you just want the Total?

It might help if you can manually include desired results for, say, 2020-Budget C2:E5

Book3.xlsx
ABCDE
1ProductIDJan Fund RaisedFeb Fund RaisedMar Fund Raised
2Product A#VALUE!0.666666680.666666680
3Product B0.333333340.333333340
4Product C0.333333340.333333340
5Product D0.666666680.666666680
6Product E000
2020-budget
Cell Formulas
RangeFormula
B2B2=IF(AND('2020-Monthly payment'!C3:C1000="Product A",'2020-Monthly payment'!D3:D1000=3),\$A\$60*SUM('2020-Monthly payment'!D3:D1000),\$A\$59*SUM('2020-Monthly payment'!D3:D1000))+(\$B\$62*'2020-One off payment'!C2:C1000)
C2:E6C2=SUMPRODUCT(('2020-Monthly payment'!\$C\$3:\$C\$1000=\$A2)*('2020-Monthly payment'!D\$3:D\$1000=3)*\$A\$60)

Your formula in B2, mine (so far) in C2:E6

#### Lioness227

##### New Member
Hi,
No worries I guess it is positive to be busy at work during this pandemic

Both sheets are supposed to be added, none is triggering the other one in the final result.
First one being monthly payments, second one being for one off payments. So ultimately we just need to add both calculations onto the final sheet.
Does that make sense?

regarding your second question for the one off payments:
Indeed, whatever the amount, 1% will be allocated to each product. There are 54 products in total so 54% will be taken, and the remaining 46% will go to another box below. I haven’t added it but indeed we should have a new row below to which we allocate 46% (relating to fees, admin costs, etc.).

Sorry it is not straight forward to explain by text but thankfully it makes sense and will confirm whether your current suggested formula is correct or if you think we should amend something?

many thanks again

#### Lioness227

##### New Member
Hi @jtakw,

assuming you are busy with work, just hoping it all makes sense. If not I can send you the file via email if possible?

Many thanks,

Elodie

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,533
Messages
5,854,291
Members
431,636
Latest member
shabbas313

### 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