SumIf Function Question

[RyanH]

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

 

[Arvi Laanemets]

Hi


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

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

=SUMPRODUCT(--('Global
Schedule'!$J$3:$J$10000>DATE(YEAR(TODAY(),MONTH(TODAY()+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)!

 

[Pete_UK]

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]

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

 

[Arvi Laanemets]

Hi

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.Range3)
, 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.

 

[David Biddulph]

Try a Google for the phrase "double unary minus".

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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