{SUM(IF((ARRRAY FORMULA)}

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)}
 
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)
 
One way (not array-entered):

=SUMPRODUCT(--(A1:A50=DATE(2007,9,10)),--(B1:B50<250),C1:C50)
 
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)}
Click to expand...

One way:
=SUM(IF(($A$1:$A$24=DATE(2007,9,10))*($B1:$B24<250),$C1:$C24))
 
{(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
 
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.
 
Minor tweak...

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

Ctrl+Shift+Enter, not just enter
 
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
 
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).
 
Back
Top