table:
1.5.2006 blue
2.5.2006 green
30.4.2005 green
21.5.2006 yellow
21.6.2006 blue
how can I count all the green guys with the datum after 1.5.2005?
countif will not probably work...
table:
1.5.2006 blue
2.5.2006 green
30.4.2005 green
21.5.2006 yellow
21.6.2006 blue
how can I count all the green guys with the datum after 1.5.2005?
countif will not probably work...
I will assume the first column are dates
With dates in column A and colours in B
=SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"mg" <[email protected]> wrote in message
news:[email protected]...
> table:
> 1.5.2006 blue
> 2.5.2006 green
> 30.4.2005 green
> 21.5.2006 yellow
> 21.6.2006 blue
>
> how can I count all the green guys with the datum after 1.5.2005?
> countif will not probably work...
>
Hi,
Just out of interest more than anything but what is the signifcance of the "--" that appears before each argument as opposed to "*", that Excel Help puts between the two arguments?
Cheers,
Originally Posted by Bernard Liengme
thanx a lot, that's exactly what I have been looking for.
You've helped me a lot.
Bernard Liengme pÃ*Å¡e:
> I will assume the first column are dates
> With dates in column A and colours in B
> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "mg" <[email protected]> wrote in message
> news:[email protected]...
> > table:
> > 1.5.2006 blue
> > 2.5.2006 green
> > 30.4.2005 green
> > 21.5.2006 yellow
> > 21.6.2006 blue
> >
> > how can I count all the green guys with the datum after 1.5.2005?
> > countif will not probably work...
> >
>
>
>
what if i need to use a range in the date f.e. from 1.4.2005 to 2.5.2005?
Bernard Liengme pÃ*Å¡e:
> I will assume the first column are dates
> With dates in column A and colours in B
> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "mg" <[email protected]> wrote in message
> news:[email protected]...
> > table:
> > 1.5.2006 blue
> > 2.5.2006 green
> > 30.4.2005 green
> > 21.5.2006 yellow
> > 21.6.2006 blue
> >
> > how can I count all the green guys with the datum after 1.5.2005?
> > countif will not probably work...
> >
>
>
>
Hi
The -- coerces a TRUE result into a 1, and a FALSE result into a 0. This can
then be used to calculate with.
If you type =FALSE in a cell, it will give the result of FALSE. If you type
=--FALSE into a cell, you will get 0 as the result. Other ways of doing the
same job are to use a * to multiply or even a +0. It's just a way of making
Excel treat logical results as numbers.
Hope this helps.
--
Andy.
"giantwolf" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> Just out of interest more than anything but what is the signifcance of
> the "--" that appears before each argument as opposed to "*", that
> Excel Help puts between the two arguments?
>
> Cheers,
>
>
>
>
> Bernard Liengme Wrote:
>> I will assume the first column are dates
>> With dates in column A and colours in B
>> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
>> best wishes
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "mg" <[email protected]> wrote in message
>> news:[email protected]...
>> > table:
>> > 1.5.2006 blue
>> > 2.5.2006 green
>> > 30.4.2005 green
>> > 21.5.2006 yellow
>> > 21.6.2006 blue
>> >
>> > how can I count all the green guys with the datum after 1.5.2005?
>> > countif will not probably work...
>> >
>
>
> --
> giantwolf
> ------------------------------------------------------------------------
> giantwolf's Profile:
> http://www.excelforum.com/member.php...o&userid=24718
> View this thread: http://www.excelforum.com/showthread...hreadid=383850
>
Hi
Try something like this:
=SUMPRODUCT(--(A1:A5>=DATE(2006,4,1))*(A1:A5<=DATE(2006,5,2)),--(B1:B5="green"))
--
Andy.
"mg" <[email protected]> wrote in message
news:[email protected]...
> what if i need to use a range in the date f.e. from 1.4.2005 to 2.5.2005?
>
> Bernard Liengme píse:
>
>> I will assume the first column are dates
>> With dates in column A and colours in B
>> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
>> best wishes
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "mg" <[email protected]> wrote in message
>> news:[email protected]...
>> > table:
>> > 1.5.2006 blue
>> > 2.5.2006 green
>> > 30.4.2005 green
>> > 21.5.2006 yellow
>> > 21.6.2006 blue
>> >
>> > how can I count all the green guys with the datum after 1.5.2005?
>> > countif will not probably work...
>> >
>>
>>
>>
or
=SUMPRODUCT(--(A1:A5>=--"2006-04-01"),-(A1:A5<=--"2006-05-02"),--(B1:B5="gre
en"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
<Andy B> wrote in message news:[email protected]...
> Hi
>
> Try something like this:
>
=SUMPRODUCT(--(A1:A5>=DATE(2006,4,1))*(A1:A5<=DATE(2006,5,2)),--(B1:B5="gree
n"))
>
>
> --
> Andy.
>
>
> "mg" <[email protected]> wrote in message
> news:[email protected]...
> > what if i need to use a range in the date f.e. from 1.4.2005 to
2.5.2005?
> >
> > Bernard Liengme píse:
> >
> >> I will assume the first column are dates
> >> With dates in column A and colours in B
> >> =SUMPRODUCT(--(A1:A5>DATE(2006,5,1)),--(B1:B5="green"))
> >> best wishes
> >> --
> >> Bernard V Liengme
> >> www.stfx.ca/people/bliengme
> >> remove caps from email
> >>
> >> "mg" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > table:
> >> > 1.5.2006 blue
> >> > 2.5.2006 green
> >> > 30.4.2005 green
> >> > 21.5.2006 yellow
> >> > 21.6.2006 blue
> >> >
> >> > how can I count all the green guys with the datum after 1.5.2005?
> >> > countif will not probably work...
> >> >
> >>
> >>
> >>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks