Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif to return an intersection? | Excel Worksheet Functions | |||
offset? match index? sumif? 2way look-up? | Excel Worksheet Functions | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
Help with SUMIF, INDEX, LOOKUP Please !! | Excel Worksheet Functions | |||
Help w/ SUMIF ; INDEX | Excel Worksheet Functions |