Hi,
Hope you're doing well.
In column E, I used an if statement to calculate column B to get the revenue.
The question is: how can I implement the same equation for columns B, C, and D in Column E?
I don't want to duplicate the work on each column separately.
Regards,
Hello Mohammed,
As you don't mention what Excel version you are using I only mention two simple solutions, which includes using Pivot Table to view the result or using helper columns. See attached file.
Customer Register (2)
What I have done is to adjust your data table to be in a tabular format. To be able to distinguish which rows each data belongs to I have added an index column. Then it is sufficient to use the formula you have written to calculate the sum of money. I then use a Pivot Table to view the sum per month + the grand total for all three months.
Customer Register (3)
Keeping your data table, but have added two more columns for July and August, just copied your formula to the right and then sum the three columns. It's nothing wrong with using helper columns, they can be hidden if you don't want to show them.
I hope this gives some guidance forward.
Br,
Anders
Dear Anders,
I appreciate your help in answering my question but was looking for an alternative. I would like to find the grand total for all three months in one column by applying the same equation.
Is there a way to use this formula to sum the three months in one column without putting each month in its own column?
=IF($A2="Citizen Account",
IF(B2<=4000,B2*0.014,
IF(B2<=6000,((B2-4000)*0.017)+(2000*0.014),
((B2-6000)*0.03)+(2000*0.014)+(2000*0.017))),
IF($A2="Additional accounts",
IF(B2<=4000,B2*0.02,
IF(B2<=6000,((B2-4000)*0.025)+(2000*0.02),
((B2-6000)*0.03)+(2000*0.02)+(2000*0.025))),
IF(B2<=4000,B2*0.01,
IF(B2<=6000,((B2-4000)*0.013)+(2000*0.01),
((B2-4000)*0.02)+(2000*0.01)+(2000*0.013)))))
Regards,
Mohammed
