Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumIf Function Question
I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows
are used as a header. In Col. L there are dates and in Col. N there are prices. At any given time Col. L cells may or may not have a date. I would like to use a formula so it will automatically update, as opposed to VBA where I would need an event to update the calculations below. I would like the formula placed in a worksheet labeled 'Summary'. 1.) Sum all cells in Col. N if the cell in the same row in Col. L has a value. This is what I got, but it does not work: =SUMIF('Global Schedule'!L:L,ISBLANK() = FALSE,'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. L is a date within the current month. 3.) Sum all cells in Col. N if the cell in the same row in Col. L is a date within the next month. 4.) Sum all cells in Col. N if the cell in the same row in Col. L is a date after the next month. Thanks, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumIf Function Question
Hi Ryan,
I have not tested this nut it should point you in the right direction 1.) *Sum all cells in Col. N if the cell in the same row in Col. L has a value. *This is what I got, but it does not work: =SUMIF('Global Schedule'!L:L,ISBLANK() = FALSE,'Global Schedule'!N:N) change to: =SUMIF('Global Schedule'!L:L,<"",'Global Schedule'!N:N) 2.) *Sum all cells in Col. N if the cell in the same row in Col. L is a date within the current month. something like: =SUMIF(Month('Global Schedule'!L:L),Month($A$1),'Global Schedule'!N:N) 3.) *Sum all cells in Col. N if the cell in the same row in Col. L is a date within the next month. =SUMIF(Month('Global Schedule'!L:L),Month($A$1)+1,'Global Schedule'! N:N) 4.) *Sum all cells in Col. N if the cell in the same row in Col. L is a date after the next month. =SUMIF(Month('Global Schedule'!L:L),Month($A$1)+2,'Global Schedule'! N:N) Where cell A1 contains a date within the required current month Regards Michael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumIf Function Question
Step 1: works great!
Step 2,3, & 4 I'm having issues with. They seem to make sense, but I'm not sure why they will not work. This is what I put. Step 2: =SUMIF(Month('Global Schedule'!L:L),"="&MONTH(NOW()),'Global Schedule'!N:N) Step 3: =SUMIF(Month('Global Schedule'!L:L),"="&MONTH(NOW())+1,'Global Schedule'!N:N) Step 4: =SUMIF(Month('Global Schedule'!L:L),""&MONTH(NOW())+1,'Global Schedule'!N:N) Thanks, Ryan "michael.beckinsale" wrote: Hi Ryan, I have not tested this nut it should point you in the right direction 1.) Sum all cells in Col. N if the cell in the same row in Col. L has a value. This is what I got, but it does not work: =SUMIF('Global Schedule'!L:L,ISBLANK() = FALSE,'Global Schedule'!N:N) change to: =SUMIF('Global Schedule'!L:L,<"",'Global Schedule'!N:N) 2.) Sum all cells in Col. N if the cell in the same row in Col. L is a date within the current month. something like: =SUMIF(Month('Global Schedule'!L:L),Month($A$1),'Global Schedule'!N:N) 3.) Sum all cells in Col. N if the cell in the same row in Col. L is a date within the next month. =SUMIF(Month('Global Schedule'!L:L),Month($A$1)+1,'Global Schedule'! N:N) 4.) Sum all cells in Col. N if the cell in the same row in Col. L is a date after the next month. =SUMIF(Month('Global Schedule'!L:L),Month($A$1)+2,'Global Schedule'! N:N) Where cell A1 contains a date within the required current month Regards Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF question (although, I think I'm using the wrong function) | Excel Discussion (Misc queries) | |||
SumIf Function Question | Excel Worksheet Functions | |||
function question (sumif countif conditional) | Excel Worksheet Functions | |||
SUMIF function question | Excel Worksheet Functions | |||
Countif/Sumif function question | Excel Worksheet Functions |