+ Reply to Thread
Results 1 to 8 of 8

countif, sumproduct

  1. #1
    mg
    Guest

    countif, sumproduct

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


  2. #2
    Bernard Liengme
    Guest

    Re: countif, sumproduct

    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" <mg@discussions.microsoft.com> wrote in message
    news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
    > 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...
    >




  3. #3
    Registered User
    Join Date
    06-28-2005
    Posts
    48
    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,




    Quote Originally Posted by Bernard Liengme
    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" <mg@discussions.microsoft.com> wrote in message
    news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
    > 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...
    >

  4. #4
    mg
    Guest

    Re: countif, sumproduct

    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" <mg@discussions.microsoft.com> wrote in message
    > news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
    > > 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...
    > >

    >
    >
    >


  5. #5
    mg
    Guest

    Re: countif, sumproduct

    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" <mg@discussions.microsoft.com> wrote in message
    > news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
    > > 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...
    > >

    >
    >
    >


  6. #6
    Guest

    Re: countif, sumproduct

    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" <giantwolf.1rhs2p_1120223313.6087@excelforum-nospam.com> wrote
    in message news:giantwolf.1rhs2p_1120223313.6087@excelforum-nospam.com...
    >
    > 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" <mg@discussions.microsoft.com> wrote in message
    >> news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
    >> > 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
    >




  7. #7
    Guest

    Re: countif, sumproduct

    Hi

    Try something like this:
    =SUMPRODUCT(--(A1:A5>=DATE(2006,4,1))*(A1:A5<=DATE(2006,5,2)),--(B1:B5="green"))


    --
    Andy.


    "mg" <mg@discussions.microsoft.com> wrote in message
    news:44CF5C68-B259-4BE8-B66D-C5CED941BA0D@microsoft.com...
    > what if i need to use a range in the date f.e. from 1.4.2005 to 2.5.2005?
    >
    > Bernard Liengme pse:
    >
    >> 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" <mg@discussions.microsoft.com> wrote in message
    >> news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
    >> > 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...
    >> >

    >>
    >>
    >>




  8. #8
    Bob Phillips
    Guest

    Re: countif, sumproduct

    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:uWejjTlfFHA.3584@TK2MSFTNGP09.phx.gbl...
    > Hi
    >
    > Try something like this:
    >

    =SUMPRODUCT(--(A1:A5>=DATE(2006,4,1))*(A1:A5<=DATE(2006,5,2)),--(B1:B5="gree
    n"))
    >
    >
    > --
    > Andy.
    >
    >
    > "mg" <mg@discussions.microsoft.com> wrote in message
    > news:44CF5C68-B259-4BE8-B66D-C5CED941BA0D@microsoft.com...
    > > what if i need to use a range in the date f.e. from 1.4.2005 to

    2.5.2005?
    > >
    > > Bernard Liengme pse:
    > >
    > >> 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" <mg@discussions.microsoft.com> wrote in message
    > >> news:763AA6C4-EBDA-4769-B9BF-A4712E190A91@microsoft.com...
    > >> > 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...
    > >> >
    > >>
    > >>
    > >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1