Formula to extract data from another sheet based on 3 uniques

[Abhimanyu]

Dear All,

Apologies, if I am putting this question on the wrong page.

I have a data sheet which has the following:

• Revenue nos (Actual and Budgeted) for different product lines of a Business Unit
• This nos are monthwise

1. Now on a different sheet, I am trying to create a basic interface, wherein I have created two similar drop downs for dates and just below them, one is for Actual and the other for budgeted. The problem I am facing is, how do I extract the data for a specific product line which will change based on the dates picked from the drop down. Can you suggest a formula
2. Though this is the next stage of the work, can you please suggest how can I put colored dots against each revenue no signifying the variance (color dot deep red if variance is more than 45% and so forth

Thanks again friends for your time and the help to excel in Excel!

Regards,
Abhimanyu

 

[Hui]

Have a read of these two posts:

Lookup and summarise a range based on Criteria
Single Criteria
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
Multiple Criteria
http://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/

But read and understand the single criteria first as the Multi criteria builds on it

 

[Abhimanyu]

Have a read of these two posts:

Lookup and summarise a range based on Criteria
Single Criteria
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
Multiple Criteria
http://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/

But read and understand the single criteria first as the Multi criteria builds on it

Thanks Hui.

Will learn and comeback if I face a hurdle

 

[Abhimanyu]

Dear Hui,

I am still not able to derive the formula. I am attaching a sample file. Would be grateful if you can recommend me the right way to do it.

The sample file, has an output sheet, where I want to extract the revenue values from the data sheet based on the "product line", and other unique's, "Month", "Actual", "Budgeted".

 

[Hui]

C6:
=SUMPRODUCT((Data!$A$6:$A$26=$B6)*(Data!$B$3:$J$3=C$5)*(Data!$B$4:$J$4=C$4)*(Data!$B$6:$J$26))
Copy across and down

Note that how you described your initial question or how I interpreted it were out of sync
This solution isn't inline with my previous suggestion
This is why it is so important to post files, they eliminate assumptions

If you want to understand how this solution works read:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[Abhimanyu]

C6:
=SUMPRODUCT((Data!$A$6:$A$26=$B6)*(Data!$B$3:$J$3=C$5)*(Data!$B$4:$J$4=C$4)*(Data!$B$6:$J$26))
Copy across and down

Note that how you described your initial question or how I interpreted it were out of sync
This solution isn't inline with my previous suggestion
This is why it is so important to post files, they eliminate assumptions

If you want to understand how this solution works read:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

Thanks Hui.

It was my fault. I should have explained it in a better way.

Thanks again for the help. Works perfect

 

[Hui]

For Q2 just add an extra column and some Conditional Formatting



see attached:

 

[Abhimanyu]

For Q2 just add an extra column and some Conditional Formatting

View attachment 18985

see attached:

Dear Hui,

Trust you are well.

I am returning on the last post. First of all, I am indebted for the help. If you ever plan to visit this side, I will be happy to be your host !

Coming back to the dashboard, with your sumproduct formula, I have got all the data into the output sheet. Have a filter now to select the monthwise data. I have created two thermo charts. I also have the conditional formatting (indicator signals) you helped me with.

Now my organisation being too naive in using excel for analysis, wanted to know what other important indicators can I add in a graphical form. Note, the data I would have every month will be , "Revenue; budgeted and actual", all product wise. Apart from these I can also get sales per sales rep. The whole idea is to make the page more meaningful by adding only things which will make sense to the CEO.

Thanks.
Abhimanyu

 

[Hui]

Abhimanyu

Thanx for the kind words

You may also want to look at Map outputs where different zones can be colored according to sales etc, they add real pazazz to dashboards when done well

There are many examples of these in the various Dashboard Contests here