Hi everyone
I have been looking at this all day and I cannot fathom how to create a formula for this. I have multiple criteria - I am essentially looking for a way to pull data taking into consideration the following:
I need to find the actual (or target) value for mexico (country) for sales (function) in jan (month) for the business selected.
This is my summary table:
A1 is a drop-down depending on which business I am looking at. B-G should summarize the sales function across 3 different countries showing target or actual. Col I to P is the same but for the marketing function.
</tbody>
My data is as follows: data is in col C-Z for sales and i have data in col AA to AX for Marketing shown in the same way I showed the sales data below:
<tbody>
What formula(s) do I use that will help me look this up? Essentially if i change the business I want the table to repopulate with the new businesses' data. I tried sumifs but it cannot look up multiple columns so I tried index and match but i don't know how to put these together or if you can put it together using so many criteria.
Please help!
Thanks in advance
CC
I have been looking at this all day and I cannot fathom how to create a formula for this. I have multiple criteria - I am essentially looking for a way to pull data taking into consideration the following:
I need to find the actual (or target) value for mexico (country) for sales (function) in jan (month) for the business selected.
This is my summary table:
A1 is a drop-down depending on which business I am looking at. B-G should summarize the sales function across 3 different countries showing target or actual. Col I to P is the same but for the marketing function.
| Business 1 | Sales | Sales | Sales | Sales | Sales | Sales | Marketing | Marketing | Marketing | Marketing | Marketing | Marketing | |
| Mexico | Mexico | Brazil | Brazil | Argentina | Argentina | Mexico | Mexico | Brazil | Brazil | Argentina | Argentina | ||
| Target | Actual | Target | Actual | Target | Actual | Target | Actual | Target | Actual | Target | Actual | ||
| Jan | |||||||||||||
| Feb | |||||||||||||
| Mar | |||||||||||||
| Apr | |||||||||||||
| May | |||||||||||||
| Jun | |||||||||||||
| Jul | |||||||||||||
| Aug | |||||||||||||
| Sep | |||||||||||||
| Oct | |||||||||||||
| Nov | |||||||||||||
| Dec |
</tbody>
My data is as follows: data is in col C-Z for sales and i have data in col AA to AX for Marketing shown in the same way I showed the sales data below:
| Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | Sales | ||
| Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Target | Target | Target | Target | Target | Target | Target | Target | Target | Target | Target | Target | ||
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | ||
| Mexico | Total | ||||||||||||||||||||||||
| Brazil | Total | ||||||||||||||||||||||||
| Argentina | Total | ||||||||||||||||||||||||
| Mexico | Business 1 | ||||||||||||||||||||||||
| Brazil | Business 1 | ||||||||||||||||||||||||
| Argentina | Business 1 | ||||||||||||||||||||||||
| Mexico | Business 2 | ||||||||||||||||||||||||
| Brazil | Business 2 | ||||||||||||||||||||||||
| Argentina | Business 2 | ||||||||||||||||||||||||
| Mexico | Business 3 | ||||||||||||||||||||||||
| Brazil | Business 3 | ||||||||||||||||||||||||
| Argentina | Business 3 |
<tbody>
What formula(s) do I use that will help me look this up? Essentially if i change the business I want the table to repopulate with the new businesses' data. I tried sumifs but it cannot look up multiple columns so I tried index and match but i don't know how to put these together or if you can put it together using so many criteria.
Please help!
Thanks in advance
CC
Last edited by a moderator:
although I am not sure I understand it. Would need to sit and break it down sometime to do that.
