{SUM(IF((ARRRAY FORMULA)}
 

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)}


--
bookman

{SUM(IF((ARRRAY FORMULA)}
 

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 )

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bookman3" wrote in message
...
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)}


--
bookman

{SUM(IF((ARRRAY FORMULA)}
 

One way (not array-entered):

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

In article ,
bookman3 wrote:

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)}

{SUM(IF((ARRRAY FORMULA)}
 

On Oct 17, 6:14 pm, bookman3
wrote:
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)}

--
bookman

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

{SUM(IF((ARRRAY FORMULA)}
 

{(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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bookman3" wrote:
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)}


--
bookman

{SUM(IF((ARRRAY FORMULA)}
 

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.

"bookman3" wrote:

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)}


--
bookman

{SUM(IF((ARRRAY FORMULA)}
 

Minor tweak...

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

Ctrl+Shift+Enter, not just enter


"bookman3" wrote:

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)}


--
bookman

{SUM(IF((ARRRAY FORMULA)}
 

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
--
bookman


"JMB" wrote:

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.

"bookman3" wrote:

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)}


--
bookman

{SUM(IF((ARRRAY FORMULA)}
 

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).
--
David Biddulph

"Teethless mama" wrote in message
...
Minor tweak...

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

Ctrl+Shift+Enter, not just enter

"bookman3" wrote:

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)}

--
bookman