HELP!
in [Functions]
|
Prev: In new Office 2007 EXCEL, can more the 8 IF statements be used?
Next: Complicated IF statement - desperately need some help
From: TJ on 24 Mar 2010 10:40 How can i combine two formulars together? =COUNTIF(L9:L453,"Deal*") =COUNTIF(A9:A452,"15/03/2010") Im trying to find out how many DEALS on a certain date
From: Mike H on 24 Mar 2010 10:48 Hi, Like this =SUMPRODUCT((A9:A453=DATE(2010,3,15))*(L9:L453="Deal")) But in practice I'd keep the lookup date in a cell =SUMPRODUCT((A9:A453=A1)*(L9:L453="Deal")) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "TJ" wrote: > How can i combine two formulars together? > > =COUNTIF(L9:L453,"Deal*") > =COUNTIF(A9:A452,"15/03/2010") > > Im trying to find out how many DEALS on a certain date
From: TJ on 24 Mar 2010 12:36 Thanks for the reply Mike H. Sorry i forgot to mention that the word DEAL has a number next to it such as... DEAL1, DEAL2 and DEAL3, how would i specify how many DEAL on a certain day using the formular below? ... so it just picks up the word DEAL. Thank you "Mike H" wrote: > Hi, > > Like this > > =SUMPRODUCT((A9:A453=DATE(2010,3,15))*(L9:L453="Deal")) > > But in practice I'd keep the lookup date in a cell > > =SUMPRODUCT((A9:A453=A1)*(L9:L453="Deal")) > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "TJ" wrote: > > > How can i combine two formulars together? > > > > =COUNTIF(L9:L453,"Deal*") > > =COUNTIF(A9:A452,"15/03/2010") > > > > Im trying to find out how many DEALS on a certain date
From: Luke M on 24 Mar 2010 13:21 SUMPRODUCT((A9:A453=A1)*(LEFT(L9:L453,4)="Deal")) -- Best Regards, Luke M "TJ" <TJ(a)discussions.microsoft.com> wrote in message news:CCD15D32-7A11-4E72-9008-48D99D01BD02(a)microsoft.com... > Thanks for the reply Mike H. > > Sorry i forgot to mention that the word DEAL has a number next to it such > as... DEAL1, DEAL2 and DEAL3, how would i specify how many DEAL on a > certain > day using the formular below? ... so it just picks up the word DEAL. > > Thank you > > > > "Mike H" wrote: > >> Hi, >> >> Like this >> >> =SUMPRODUCT((A9:A453=DATE(2010,3,15))*(L9:L453="Deal")) >> >> But in practice I'd keep the lookup date in a cell >> >> =SUMPRODUCT((A9:A453=A1)*(L9:L453="Deal")) >> -- >> Mike >> >> When competing hypotheses are otherwise equal, adopt the hypothesis that >> introduces the fewest assumptions while still sufficiently answering the >> question. >> >> >> "TJ" wrote: >> >> > How can i combine two formulars together? >> > >> > =COUNTIF(L9:L453,"Deal*") >> > =COUNTIF(A9:A452,"15/03/2010") >> > >> > Im trying to find out how many DEALS on a certain date
From: TJ on 25 Mar 2010 05:21
Thats done the job Thanks Luke "Luke M" wrote: > SUMPRODUCT((A9:A453=A1)*(LEFT(L9:L453,4)="Deal")) > > -- > Best Regards, > > Luke M > "TJ" <TJ(a)discussions.microsoft.com> wrote in message > news:CCD15D32-7A11-4E72-9008-48D99D01BD02(a)microsoft.com... > > Thanks for the reply Mike H. > > > > Sorry i forgot to mention that the word DEAL has a number next to it such > > as... DEAL1, DEAL2 and DEAL3, how would i specify how many DEAL on a > > certain > > day using the formular below? ... so it just picks up the word DEAL. > > > > Thank you > > > > > > > > "Mike H" wrote: > > > >> Hi, > >> > >> Like this > >> > >> =SUMPRODUCT((A9:A453=DATE(2010,3,15))*(L9:L453="Deal")) > >> > >> But in practice I'd keep the lookup date in a cell > >> > >> =SUMPRODUCT((A9:A453=A1)*(L9:L453="Deal")) > >> -- > >> Mike > >> > >> When competing hypotheses are otherwise equal, adopt the hypothesis that > >> introduces the fewest assumptions while still sufficiently answering the > >> question. > >> > >> > >> "TJ" wrote: > >> > >> > How can i combine two formulars together? > >> > > >> > =COUNTIF(L9:L453,"Deal*") > >> > =COUNTIF(A9:A452,"15/03/2010") > >> > > >> > Im trying to find out how many DEALS on a certain date > > > . > |