+ Reply to Thread
Results 1 to 9 of 9

logic that returns a count

  1. #1
    d_kight
    Guest

    logic that returns a count

    I am trying to compare one column to another column and if the two conditions
    I am looking at are true, I would like it to be counted. Eventually adding
    all occurances of those two conditions.

    EX
    A B C D F
    G
    1 5/14/2006 Indiana/01 John Doe enroll mail enter
    2 5/14/2006 Indiana/02 John Doe enroll mail enter
    3 5/14/2006 Indiana/01 Jane Plur enroll mail enter
    4 5/14/2006 Indiana/03 John Paul enroll mail enter
    5 5/14/2006 Indiana/01 Joe Dirt salary fax

    I want the result to capture the "B" column if it says "Indiana/01" and then
    the "D" column if it says "enroll" and add all of the occuances of both of
    those and spit out a value.
    I would greatly appreciate any help I can get on the matter.

    thanks Danny



  2. #2
    Pete_UK
    Guest

    Re: logic that returns a count

    Try this array* formula:

    =SUM(IF((B1:B500="Indiana/01")*(D1:D500="enroll"),1,0))

    * As this is an array formula, once you have typed it in (or
    subsequently edit it) you need to use CTRL-SHIFT-ENTER instead of just
    ENTER. If you do this correctly, then Excel will wrap curly braces { }
    around the formula - you must not type these yourself.

    I have assumed the data is in rows 1 to 500 - adjust to suit.

    Hope this helps.

    Pete


  3. #3
    Ian P
    Guest

    RE: logic that returns a count

    Enter a formula in column H:

    =if(AND(b1="Indiana/01",d1="enroll"),1,0)

    Drag this formula down the spreadsheet and then at the bottom of that column
    use the formula:

    =sum(d1:d###) where ### is the row number of the last entry.

    HTH

    Ian

    "d_kight" wrote:

    > I am trying to compare one column to another column and if the two conditions
    > I am looking at are true, I would like it to be counted. Eventually adding
    > all occurances of those two conditions.
    >
    > EX
    > A B C D F
    > G
    > 1 5/14/2006 Indiana/01 John Doe enroll mail enter
    > 2 5/14/2006 Indiana/02 John Doe enroll mail enter
    > 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter
    > 4 5/14/2006 Indiana/03 John Paul enroll mail enter
    > 5 5/14/2006 Indiana/01 Joe Dirt salary fax
    >
    > I want the result to capture the "B" column if it says "Indiana/01" and then
    > the "D" column if it says "enroll" and add all of the occuances of both of
    > those and spit out a value.
    > I would greatly appreciate any help I can get on the matter.
    >
    > thanks Danny
    >
    >


  4. #4
    Ardus Petus
    Guest

    Re: logic that returns a count

    =SUMPRODUCT((B1:B999="Indiana/01")*(D1:D999="enroll")

    HTH
    --
    AP

    "d_kight" <dkight@discussions.microsoft.com> a écrit dans le message de
    news: BDC1700E-1649-46DD-9B72-056A1247EC2A@microsoft.com...
    >I am trying to compare one column to another column and if the two
    >conditions
    > I am looking at are true, I would like it to be counted. Eventually
    > adding
    > all occurances of those two conditions.
    >
    > EX
    > A B C D F
    > G
    > 1 5/14/2006 Indiana/01 John Doe enroll mail enter
    > 2 5/14/2006 Indiana/02 John Doe enroll mail enter
    > 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter
    > 4 5/14/2006 Indiana/03 John Paul enroll mail enter
    > 5 5/14/2006 Indiana/01 Joe Dirt salary fax
    >
    > I want the result to capture the "B" column if it says "Indiana/01" and
    > then
    > the "D" column if it says "enroll" and add all of the occuances of both of
    > those and spit out a value.
    > I would greatly appreciate any help I can get on the matter.
    >
    > thanks Danny
    >
    >




  5. #5
    d_kight
    Guest

    Re: logic that returns a count

    Pete,
    I think this is exactly what I was looking for. I tried something similar
    to this and it kept returning a #value? Is there a way to use this when
    there are blank cells as well?

    "Pete_UK" wrote:

    > Try this array* formula:
    >
    > =SUM(IF((B1:B500="Indiana/01")*(D1:D500="enroll"),1,0))
    >
    > * As this is an array formula, once you have typed it in (or
    > subsequently edit it) you need to use CTRL-SHIFT-ENTER instead of just
    > ENTER. If you do this correctly, then Excel will wrap curly braces { }
    > around the formula - you must not type these yourself.
    >
    > I have assumed the data is in rows 1 to 500 - adjust to suit.
    >
    > Hope this helps.
    >
    > Pete
    >
    >


  6. #6
    d_kight
    Guest

    Re: logic that returns a count

    Ardus,
    I really do enjoy the ease of the SumProduct fundtions! Now will this
    fumble if there are blank cells?

    "Ardus Petus" wrote:

    > =SUMPRODUCT((B1:B999="Indiana/01")*(D1:D999="enroll")
    >
    > HTH
    > --
    > AP
    >
    > "d_kight" <dkight@discussions.microsoft.com> a écrit dans le message de
    > news: BDC1700E-1649-46DD-9B72-056A1247EC2A@microsoft.com...
    > >I am trying to compare one column to another column and if the two
    > >conditions
    > > I am looking at are true, I would like it to be counted. Eventually
    > > adding
    > > all occurances of those two conditions.
    > >
    > > EX
    > > A B C D F
    > > G
    > > 1 5/14/2006 Indiana/01 John Doe enroll mail enter
    > > 2 5/14/2006 Indiana/02 John Doe enroll mail enter
    > > 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter
    > > 4 5/14/2006 Indiana/03 John Paul enroll mail enter
    > > 5 5/14/2006 Indiana/01 Joe Dirt salary fax
    > >
    > > I want the result to capture the "B" column if it says "Indiana/01" and
    > > then
    > > the "D" column if it says "enroll" and add all of the occuances of both of
    > > those and spit out a value.
    > > I would greatly appreciate any help I can get on the matter.
    > >
    > > thanks Danny
    > >
    > >

    >
    >
    >


  7. #7
    d_kight
    Guest

    RE: logic that returns a count

    Thanks, Ian. I Tried this but it did not seem to work the way that I was
    wanting it too. I will keep this and use it in the future for my inventory
    totals!

    "Ian P" wrote:

    > Enter a formula in column H:
    >
    > =if(AND(b1="Indiana/01",d1="enroll"),1,0)
    >
    > Drag this formula down the spreadsheet and then at the bottom of that column
    > use the formula:
    >
    > =sum(d1:d###) where ### is the row number of the last entry.
    >
    > HTH
    >
    > Ian
    >
    > "d_kight" wrote:
    >
    > > I am trying to compare one column to another column and if the two conditions
    > > I am looking at are true, I would like it to be counted. Eventually adding
    > > all occurances of those two conditions.
    > >
    > > EX
    > > A B C D F
    > > G
    > > 1 5/14/2006 Indiana/01 John Doe enroll mail enter
    > > 2 5/14/2006 Indiana/02 John Doe enroll mail enter
    > > 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter
    > > 4 5/14/2006 Indiana/03 John Paul enroll mail enter
    > > 5 5/14/2006 Indiana/01 Joe Dirt salary fax
    > >
    > > I want the result to capture the "B" column if it says "Indiana/01" and then
    > > the "D" column if it says "enroll" and add all of the occuances of both of
    > > those and spit out a value.
    > > I would greatly appreciate any help I can get on the matter.
    > >
    > > thanks Danny
    > >
    > >


  8. #8
    Ardus Petus
    Guest

    Re: logic that returns a count

    Hi, d_kight,

    Yes it will (AFAIK)

    --
    AP

    "d_kight" <dkight@discussions.microsoft.com> a écrit dans le message de
    news: 0FC2F4FC-5AB9-45F1-9B04-151A2B381205@microsoft.com...
    > Ardus,
    > I really do enjoy the ease of the SumProduct fundtions! Now will this
    > fumble if there are blank cells?
    >
    > "Ardus Petus" wrote:
    >
    >> =SUMPRODUCT((B1:B999="Indiana/01")*(D1:D999="enroll")
    >>
    >> HTH
    >> --
    >> AP
    >>
    >> "d_kight" <dkight@discussions.microsoft.com> a écrit dans le message de
    >> news: BDC1700E-1649-46DD-9B72-056A1247EC2A@microsoft.com...
    >> >I am trying to compare one column to another column and if the two
    >> >conditions
    >> > I am looking at are true, I would like it to be counted. Eventually
    >> > adding
    >> > all occurances of those two conditions.
    >> >
    >> > EX
    >> > A B C D
    >> > F
    >> > G
    >> > 1 5/14/2006 Indiana/01 John Doe enroll mail enter
    >> > 2 5/14/2006 Indiana/02 John Doe enroll mail enter
    >> > 3 5/14/2006 Indiana/01 Jane Plur enroll mail enter
    >> > 4 5/14/2006 Indiana/03 John Paul enroll mail enter
    >> > 5 5/14/2006 Indiana/01 Joe Dirt salary fax
    >> >
    >> > I want the result to capture the "B" column if it says "Indiana/01" and
    >> > then
    >> > the "D" column if it says "enroll" and add all of the occuances of both
    >> > of
    >> > those and spit out a value.
    >> > I would greatly appreciate any help I can get on the matter.
    >> >
    >> > thanks Danny
    >> >
    >> >

    >>
    >>
    >>




  9. #9
    Pete_UK
    Guest

    Re: logic that returns a count

    I think you will get #VALUE if you do not commit the formula with
    CTRL-SHIFT-ENTER or if you have errors in the columns. It should work
    with spaces in either B or D because these will return FALSE, and so 0
    will be added to the cumulative sum - why not try it?

    Pete


+ 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