{SUM(IF((ARRRAY FORMULA)}

[bookman3]

Hi

I use this formula a lot.
My question is, is it possible to use it with dates either by actually
entering a date as below or referencing the date to a cell?

So far I can't get anything with dates to work.
eg

{(sum(if(($a$1:$a$50=9/10/07)*($b1:$b2<250),$c1:$c50)}

 

[Don Guillett]

easier if you use a cell. You can also use sumproduct which does NOT have to
be array entered.

=sumproduct(($a$1:$a$50=b2)*($b1:$b2<250),$c1:$c50)

 

[JE McGimpsey]

One way (not array-entered):

=SUMPRODUCT(--(A1:A50=DATE(2007,9,10)),--(B1:B50<250),C1:C50)

 

[JW]

Hi

I use this formula a lot.
My question is, is it possible to use it with dates either by actually
entering a date as below or referencing the date to a cell?

So far I can't get anything with dates to work.
eg

{(sum(if(($a$1:$a$50=9/10/07)*($b1:$b2<250),$c1:$c50)}

One way:
=SUM(IF(($A$1:$A$24=DATE(2007,9,10))*($B1:$B24<250),$C1:$C24))

 

[Max]

{(sum(if(($a$1:$a$50=9/10/07)*($b1:$b2<250),$c1:$c50)}

Above will work fine if you put it like this, array-entered:
=SUM(IF(($A$1:$A$50= --"10 Sep 2007")*($B1:$B50<250),$C1:$C50))
using: --"<an unambiguous date>"
with all ranges consistent in size
& an equal no. of opening/closing parens

 

[JMB]

Just so that you understand the reason why your original formula did not work
- 9/10/07 is being interpreted as 9 divided by 10 divided by 7.

 

[Teethless mama]

Minor tweak...

=sum(if(($a$1:$a$50=Datevalue("9/10/07"))*($b1:$b2<250),$c1:$c50))

Ctrl+Shift+Enter, not just enter

 

[bookman3]

Hi

Thanks for that.
I actually wasn't using that format in my formula.
What I really wanted was to be ableto ference the date range to a cell
=sum(if(($a$1:$q$50= b3 etc
where B3 is a date.
It appears from the other replies that the best way is SUMPRODUCT

 

[David Biddulph]

But (having fallen into the same trap in the past) presumably the DATEVALUE
function will return a different answer (Sept 10th or Oct 9th) from
"9/10/07" depending on the Windows Regional Settings?

Hence the safest recommendation is either to use an unambiguous text string
(such as 09 Oct 2007) in DATEVALUE, or to use DATE(2007,10,9).