Feb 04 2019 01:48 AM
Dear all,
I've got absolutely no idea where to start with this so I thought I'd come here and ask the gurus!
So I have a sheet full of sales forecasting data that I want to be able to lookup a sales forecasting stage such as Pipeline, Development, Legal, Close etc and then go through the data, pull out all the Pipeline opportunity values by QTR and add them together in a summary:
Opportunity ID | Customer | Value | Month | QTR | Stage |
Opportunity 1 | Customer 1 | £ 10,000.00 | Mar-19 | 1 | Pipeline |
Opportunity 2 | Customer 2 | £ 20,000.00 | Jun-19 | 2 | Solution Dev |
Opportunity 3 | Customer 3 | £ 30,000.00 | Sep-19 | 3 | Legal |
Opportunity 4 | Customer 1 | £ 40,000.00 | Jan-19 | 1 | Pipeline |
Opportunity 5 | Customer 2 | £ 50,000.00 | Feb-19 | 1 | Solution Dev |
Opportunity 6 | Customer 3 | £ 60,000.00 | Apr-19 | 2 | Legal |
Q1 Pipeline = xxxx
Q1 Sol Dev = xxxx
Q1 Legal = xxxx
Q1 Close = xxx
Q2 Pipeline = xxx
Etc...
I'm thinking it might be a VLOOKUP but honestly I have no idea!
Any help is appreciated! Thank you in advance,
Col
Feb 04 2019 03:04 AM
SolutionHi Col,
To sum based on criteria you may use SUMIFS https://support.office.com/en-us/article/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b
Feb 04 2019 04:23 AM
Sergei,
Perfect!!! Thank you :)
Regards,
Col
Feb 04 2019 03:04 AM
SolutionHi Col,
To sum based on criteria you may use SUMIFS https://support.office.com/en-us/article/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b