Match& Index and Sumif
 

Hi
I have a spreadsheet with a worksheet that has all the budget plans and
another worksheet with summary YTD. What I would like to do is on my
summary sheet pull through the cumulative plan to date based on the
month entered in cell G5.
So far I've managed to identify which columns need to be summed based
on what is entered in G5, by using an IF statement. I then have a sumif
(in column D)which would sum all the values for the valid month in G5.
The problem I am having is now combing this with a lookup of some sort.


I think I need to use a match & index and sumif... but not sure how to
contruct this.
There may be a simpler way. Any help is appreciated.
Below is what I hope to achieve

User enters month number into G5
Column D is then updated...based on indicator in Column C, it shows the
cumulative YTD budget.

Match& Index and Sumif
 

not too clear on your needs but try this idea
=sumproduct((month(daterange)=2)*datarange)

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
Hi
I have a spreadsheet with a worksheet that has all the budget plans and
another worksheet with summary YTD. What I would like to do is on my
summary sheet pull through the cumulative plan to date based on the
month entered in cell G5.
So far I've managed to identify which columns need to be summed based
on what is entered in G5, by using an IF statement. I then have a sumif
(in column D)which would sum all the values for the valid month in G5.
The problem I am having is now combing this with a lookup of some sort.


I think I need to use a match & index and sumif... but not sure how to
contruct this.
There may be a simpler way. Any help is appreciated.
Below is what I hope to achieve

User enters month number into G5
Column D is then updated...based on indicator in Column C, it shows the
cumulative YTD budget.

Match& Index and Sumif
 

Thanks Don, but that doesn't get around my lookup part of the problem.
Don Guillett wrote:
not too clear on your needs but try this idea
=sumproduct((month(daterange)=2)*datarange)

Match& Index and Sumif
 

What's the lookup involved?

As I understand your question, you are summing via a month entered in G5.

What do you want to do after that--copy the value to the summary sheet?
--
Brevity is the soul of wit.


" wrote:

Hi
I have a spreadsheet with a worksheet that has all the budget plans and
another worksheet with summary YTD. What I would like to do is on my
summary sheet pull through the cumulative plan to date based on the
month entered in cell G5.
So far I've managed to identify which columns need to be summed based
on what is entered in G5, by using an IF statement. I then have a sumif
(in column D)which would sum all the values for the valid month in G5.
The problem I am having is now combing this with a lookup of some sort.


I think I need to use a match & index and sumif... but not sure how to
contruct this.
There may be a simpler way. Any help is appreciated.
Below is what I hope to achieve

User enters month number into G5
Column D is then updated...based on indicator in Column C, it shows the
cumulative YTD budget.

Match& Index and Sumif
 

Based on the month entered in the summary sheet cell G5 , I want to
have the cumulative YTD budget returned next to each indicatior.
Summary Sheet
Cell G5 "Enter month"

Column C Column D
Indicator A lookup(columnC... return YTD budget based on month
in G5) *
Indicator B
Indicator C

Budget Sheet
cell above month number has formula
{if(monthnumber<=month,g5 else ""
1 2 3 4
Indicators April May June July
Indicator A 20 25 27
Indicator B

*bit Im having trouble with

Dave F wrote:
What's the lookup involved?

As I understand your question, you are summing via a month entered in G5.

What do you want to do after that--copy the value to the summary sheet?
--
Brevity is the soul of wit.