IfF/AND formula for a range of cells

Lioness227

New Member
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS
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
    33B95DBB-BDAA-48FF-977E-D8D24370C7F3.jpeg
    91.6 KB · Views: 3
  • 1658984C-6A76-4AC7-97BA-CB9555069C3D.jpeg
    1658984C-6A76-4AC7-97BA-CB9555069C3D.jpeg
    103.6 KB · Views: 3

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS
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?

Untitled spreadsheet.xlsx
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


Untitled spreadsheet.xlsx
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




Untitled spreadsheet.xlsx
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Thank you for uploading samples.

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
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS

ADVERTISEMENT

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.

many thanks for your help
 

Lioness227

New Member
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS
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.

many thanks for your help
@jtakw hopefully you saw my response here :)
 

Lioness227

New Member
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS

ADVERTISEMENT

@jtakw hope you can still help :)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS
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
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,569
Messages
5,637,098
Members
416,957
Latest member
Brovashift

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top