Hello ExcelForum Community!
I have a dataset with monthly revenue as columns and rows as different customers. M1 denotes the first month of the calendar year, M2 the second, and so on...I am trying to calculate the quarterly revenue a customer brings in across the year. The issue is that some customers do not have consecutive/consistent monthly revenue and so a customer may have revenue in M1-M2, none in M3, and then resume business in M4 (as shown in the table below & attached workbook). Consequently, a simple sum of M1-M3 revenues for the Q1 revenue by customer wouldn't work. For each customer, the Q1 revenue is equal to the first three months of revenue above 0, Q2 revenue is equal to the next three months of revenue above 0 (if data/revenue exists for the customer). Any help on a formula that would calculate the quarterly revenue by summing only the non-zero values throughout the year?
Please see below for a sample table and I've also attached excel file with the sample data.
In the table below, for customer 1 in row X, the ideal Q1 revenue calculation would be 800 (100 in M1, 200 in M3, and 500 in M4). The Q1 revenue for Customer 2 in Row 3 would be 450 (100 in M1, 250 in M2, and 100 in M3).
Customer Size M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12 Q1 Q2 Q3 Q4
Customer 1 Large 100 0 200 500 600 0 0 600 700 100 150 200 800 1400
Customer 2 Small 100 250 100 0 0 600 500 400 450 450 450 450 450 1500
Customer 3 Medium 100 0 200 500 600 0 0 600 700 100 0 0
Customer 4 Small 100 150 150 50 50 50 50 25 25 25 25 25
Thank you and pls let me know if further info is needed from my end!
Bookmarks