Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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)} |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
{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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set a 2D arrray data into a range, given the top-left cell | Excel Discussion (Misc queries) |