SumIf Function Question

[RyanH]

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

 

[michael.beckinsale]

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:

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

 

[RyanH]

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