CODEWITHSUNDEEP
excelexcel-formula
user3197575
user3197575

Reputation: 277

INDEX&MATCH excel on rows and return SUMPRODUCT

I needed some help with Excel functions as I am not really sure how to achieve this.

Basically, this picture shows my data structure:

I am aware that I can use Index& Match excel functions together for columns but I am not sure if it is possible use Index&Match together on two row conditions.

Requirement:

I have three products, product A, product B and Product C. I want to be able to quickly calculate the actual revenue and and you can see the data structure in the image above. My top-most row is month. For each month, I have 4 columns, of which 2 need to be calculated should the condition match.

This is the condition. An example, for product A, calculate the total sumproduct(multiples column actual customers x Price Actuals) for yearMonth 201501.

So basically,first, for product A, it would need to find YearMonth 201501, and when it is found, it should find columns Actual Customers and Price Actuals and return the SUMPRODUCT of Actual Customers and Price Actuals.

Is it possible to do this?

I have searched the Internet a bit and I have not found a solution.

Update:I thought I could post the follow-up question in the same thread insted of Cristiano duplicates... For the accepted answer, in the excel formula, how can it be divided by the total amount of "actual customers" for that month?

Simply adding "actual customers" with a slash before IT does not work.

Upvotes: 1

Views: 254

Answers (1)

teylyn
teylyn

Reputation: 35990

You need a cell where you specify the month you want to look up. Give that cell the name "TheMonth".

You need a cell where you specify the product you want to look up. Give that cell the name "TheProduct".

Then, assuming your screenshot starts in cell A1, you can use

=SUMPRODUCT($B$3:$I$5*($B$1:$I$1=TheMonth)*(($B$2:$I$2="Actual Customers")+($B$2:$I$2="Price - Actuals"))*($A$3:$A$5=TheProduct))

Next time, please post a link to a workbook. It's not much fun typing in the text and numbers from a screenshot in order to verify a formula.

Here is the workbook with the formula in action.

Upvotes: 1

Related Questions