SumIf Function Question
 

I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows
are used as a header. In Col. J there are dates and in Col. N there are
prices. At any given time Col. J cells may or may not have a date. I would
like
the formula placed in a worksheet labeled 'Summary'. Here is what I have,
but it does not seem to work.

1.) Sum all cells in Col. N if the cell in the same row in Col. J is a date
within the current month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global Schedule'!N:N)

2.) Sum all cells in Col. N if the cell in the same row in Col. J is a date
within the next month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global Schedule'!N:N)

3.) Sum all cells in Col. N if the cell in the same row in Col. J is a date
after the next month.
=SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global Schedule'!N:N)

Thanks,
Ryan

SumIf Function Question
 

Hi


=SUMPRODUCT(--('Global
Schedule'!$J$3:$J$10000DATE(YEAR(TODAY(),MONTH(TO DAY(),0),--('Global
Schedule'!$J3:$J$10000<DATE(YEAR(TODAY(),MONTH(TOD AY()+1,1),'Global
Schedule'!$N$2:$N$10000)

=SUMPRODUCT(--('Global
Schedule'!$J$3:$J$10000DATE(YEAR(TODAY(),MONTH(TO DAY()+1,0),--('Global
Schedule'!$J3:$J$10000<DATE(YEAR(TODAY(),MONTH(TOD AY()+2,1),'Global
Schedule'!$N$2:$N$10000)

=SUMPRODUCT(--('Global
Schedule'!$J$3:$J$10000DATE(YEAR(TODAY(),MONTH(TO DAY()+2,0),'Global
Schedule'!$N$2:$N$10000)

Adjust ranges.
NB! SUMPRODUCT doesn't allow references to whole column. Refer to range with
enough spare rows, or use dynamic named ranges instead!
NB! All ranges used in sumproduct as source ranges MUST be of same dimension
(include same number of rows)!

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"RyanH" wrote in message
...
I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows
are used as a header. In Col. J there are dates and in Col. N there are
prices. At any given time Col. J cells may or may not have a date. I
would
like
the formula placed in a worksheet labeled 'Summary'. Here is what I have,
but it does not seem to work.

1.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the current month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global
Schedule'!N:N)

2.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the next month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global
Schedule'!N:N)

3.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
after the next month.
=SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global
Schedule'!N:N)

Thanks,
Ryan

SumIf Function Question
 

Try it with SUMPRODUCT - the main caveat is that you cannot use full column
references (unless you have Excel 2007). Here's an example of the first one:

=SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global
Schedule'!N1:N1000)

I've limited this to 1000 rows - adjust if you have more.

Hope this helps.

Pete

"RyanH" wrote in message
...
I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows
are used as a header. In Col. J there are dates and in Col. N there are
prices. At any given time Col. J cells may or may not have a date. I
would
like
the formula placed in a worksheet labeled 'Summary'. Here is what I have,
but it does not seem to work.

1.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the current month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global
Schedule'!N:N)

2.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the next month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global
Schedule'!N:N)

3.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
after the next month.
=SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global
Schedule'!N:N)

Thanks,
Ryan

SumIf Function Question
 

What does the "--" portion of the formula for? (Located after "=SUMPRODUCT("
and before "(MONTH(....")


"Pete_UK" wrote:

Try it with SUMPRODUCT - the main caveat is that you cannot use full column
references (unless you have Excel 2007). Here's an example of the first one:

=SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global
Schedule'!N1:N1000)

I've limited this to 1000 rows - adjust if you have more.

Hope this helps.

Pete

"RyanH" wrote in message
...
I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows
are used as a header. In Col. J there are dates and in Col. N there are
prices. At any given time Col. J cells may or may not have a date. I
would
like
the formula placed in a worksheet labeled 'Summary'. Here is what I have,
but it does not seem to work.

1.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the current month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global
Schedule'!N:N)

2.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the next month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global
Schedule'!N:N)

3.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
after the next month.
=SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global
Schedule'!N:N)

Thanks,
Ryan

SumIf Function Question
 

Hi


"RyanH" wrote in message
...
What does the "--" portion of the formula for? (Located after
"=SUMPRODUCT("
and before "(MONTH(....")

Converts logical ecpression to its numeric value. When you are multiplying
directly in formula like
=SUMPRODUCT((Range1=Value)*(Range2=Value2)*Range3)
, then Excel does the converting for you. When you try
=SUMPRODUCT((Range1=Value),(Range2=Value2).Range3)
instead, Excel tries to multiply logical TRU/FALSE with a number, and this
does'nt work. Better will be
=SUMPRODUCT((Range1=Value)*1,(Range2=Value2)*1.Ran ge3)
, where multiplying with 1 converts all parts of formula to numbers, and
=SUMPRODUCT(--(Range1=Value),--(Range2=Value2).Range3)
does exactly same, but is considered as preferable beacuse it's faster
compared to other syntaxes.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )




"Pete_UK" wrote:

Try it with SUMPRODUCT - the main caveat is that you cannot use full
column
references (unless you have Excel 2007). Here's an example of the first
one:

=SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global
Schedule'!N1:N1000)

I've limited this to 1000 rows - adjust if you have more.

Hope this helps.

Pete

"RyanH" wrote in message
...
I have a very large Worksheet labeled 'Global Schedule'. The first 2
rows
are used as a header. In Col. J there are dates and in Col. N there
are
prices. At any given time Col. J cells may or may not have a date. I
would
like
the formula placed in a worksheet labeled 'Summary'. Here is what I
have,
but it does not seem to work.

1.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the current month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global
Schedule'!N:N)

2.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the next month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global
Schedule'!N:N)

3.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
after the next month.
=SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global
Schedule'!N:N)

Thanks,
Ryan

SumIf Function Question
 

Try a Google for the phrase "double unary minus".
--
David Biddulph

"RyanH" wrote in message
...
What does the "--" portion of the formula for? (Located after
"=SUMPRODUCT("
and before "(MONTH(....")


"Pete_UK" wrote:

Try it with SUMPRODUCT - the main caveat is that you cannot use full
column
references (unless you have Excel 2007). Here's an example of the first
one:

=SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global
Schedule'!N1:N1000)

I've limited this to 1000 rows - adjust if you have more.

Hope this helps.

Pete

"RyanH" wrote in message
...
I have a very large Worksheet labeled 'Global Schedule'. The first 2
rows
are used as a header. In Col. J there are dates and in Col. N there
are
prices. At any given time Col. J cells may or may not have a date. I
would
like
the formula placed in a worksheet labeled 'Summary'. Here is what I
have,
but it does not seem to work.

1.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the current month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global
Schedule'!N:N)

2.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the next month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global
Schedule'!N:N)

3.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
after the next month.
=SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global
Schedule'!N:N)

Thanks,
Ryan

SumIf Function Question
 

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

RyanH wrote:

What does the "--" portion of the formula for? (Located after "=SUMPRODUCT("
and before "(MONTH(....")

"Pete_UK" wrote:

Try it with SUMPRODUCT - the main caveat is that you cannot use full column
references (unless you have Excel 2007). Here's an example of the first one:

=SUMPRODUCT(--(MONTH('Global Schedule'!J1:J1000)=MONTH(NOW())),'Global
Schedule'!N1:N1000)

I've limited this to 1000 rows - adjust if you have more.

Hope this helps.

Pete

"RyanH" wrote in message
...
I have a very large Worksheet labeled 'Global Schedule'. The first 2 rows
are used as a header. In Col. J there are dates and in Col. N there are
prices. At any given time Col. J cells may or may not have a date. I
would
like
the formula placed in a worksheet labeled 'Summary'. Here is what I have,
but it does not seem to work.

1.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the current month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW()),'Global
Schedule'!N:N)

2.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
within the next month.
=SUMIF(Month('Global Schedule'!J:J),"="&MONTH(NOW())+1,'Global
Schedule'!N:N)

3.) Sum all cells in Col. N if the cell in the same row in Col. J is a
date
after the next month.
=SUMIF(Month('Global Schedule'!J:J),""&MONTH(NOW())+1,'Global
Schedule'!N:N)

Thanks,
Ryan






--

Dave Peterson