SUMPRODUCT

[Guest]

I have 3 col of information:
Col A=dates
Col B=acct numbers
Col 3=currency

I'm trying to find the sum of Col 3 based on a date range in Col A and
certain numbers in Col B

Col A Col B Col C
12/19/06 42016 500
01/03/07 42011 200
03/15/07 42011 150
03/31/07 42011 10


If the question was: Total of Col C during Jan-Mar 2007 in acct 42011 or
42012
Answer: 160

Here is what I was trying to use and am getting #VALUE! error:
=SUMPRODUCT((A1:A4=< 39172)*((F1:F4={42011,42012})*(C2:C500)))

 

[Guest]

All the range sizes must be the same

=SUMPRODUCT((A1:A4=< 39172)*((F1:F4={42011,42012})*(C1:C4)))

would be valid

and is answer 360?

 

[David Biddulph]

Firstly: Your total is 360 not 160
Secondly: You've looked in column F where your data is in column B
Thirdly: You want the array in column C to correspond to those in columns A
and B

So far, try =SUMPRODUCT((A1:A4<=39172)*((B1:B4={42011,42012})*(C1:C4)))

Fourthly, you haven't tested for the lower limit on date.
Fifthly, ... [I haven't looked that far.]

 

[Guest]

A better solution .....check for start/end period rather than "less than" test

=SUMPRODUCT(($A$1:$A$4>=DATE(2007,1,1))*($A$1:$A$4<=DATE(2007,3,31))*($F$1:$F$4={42011,42012})*($C$1:$C$4))

 

[Guest]

This works perfectly - please excuse my errors due to copying from my
original spreadsheet (columns were wrong and sum was in fact 360). My
problem was the format - the entries in col F (on my original) were formatted
as General, not Number so I couldn't calculate unless I put the quotes around
them ("42011","42012") HOWEVER I've never seen the start/end period as you
have it and that is absolutely the BEST! Thank you so very, very much!