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: