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 blank
and the cell 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 blank
and the cell 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 blank
and the cell in Col. L is a date after the next month.

Thanks in Advance,
Ryan

Hi,

Your first formula should be:-

=SUMPRODUCT(('Global Schedule'!L1:L1000<"")*('Global Schedule'!N1:N1000))

But the logic for other formulas doesn't make sense
For example
2.) Sum all cells in Col. N if the cell in the same row in Col. L is blank
and the cell in Col. L is a date within the current month.

Column L can't be both blank and a date with the current month in

Mike

"RyanH" wrote:

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 blank
and the cell 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 blank
and the cell 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 blank
and the cell in Col. L is a date after the next month.

Thanks in Advance,
Ryan

Your right, that makes no sense. It should be:

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


"Mike H" wrote:

Hi,

Your first formula should be:-

=SUMPRODUCT(('Global Schedule'!L1:L1000<"")*('Global Schedule'!N1:N1000))

But the logic for other formulas doesn't make sense
For example
2.) Sum all cells in Col. N if the cell in the same row in Col. L is blank
and the cell in Col. L is a date within the current month.

Column L can't be both blank and a date with the current month in

Mike

"RyanH" wrote:

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 blank
and the cell 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 blank
and the cell 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 blank
and the cell in Col. L is a date after the next month.

Thanks in Advance,
Ryan