+ Reply to Thread
Results 1 to 8 of 8

[SOLVED] Count of items using multiple criteria

  1. #1
    mbparks
    Guest

    [SOLVED] Count of items using multiple criteria

    I am working on a spreadsheet that tracks when an item is requested, the type
    of request it is, when it returns and the age of the outstanding items.
    I have used formulas to automatically enter the type of item being requested
    and the age of the outstanding items.
    What I want to do is count the number of outstanding item#1s that are over
    40 days old. The type of item is in Col. I and the age of o/s items is Col.
    V.
    I've tried to use the sumproduct formula but I'm not getting the right totals.
    Help is greatly appreciated.


  2. #2
    KL
    Guest

    Re: Count of items using multiple criteria

    Hi there,

    The way you describe it the following formula should work, I guess:

    =SUMPRODUCT((I2:I100=1)*(V2:V100>40))

    unless any of your data (type or age) are in text format of course.

    Regards,
    KL

    "mbparks" <[email protected]> wrote in message
    news:[email protected]...
    >I am working on a spreadsheet that tracks when an item is requested, the
    >type
    > of request it is, when it returns and the age of the outstanding items.
    > I have used formulas to automatically enter the type of item being
    > requested
    > and the age of the outstanding items.
    > What I want to do is count the number of outstanding item#1s that are over
    > 40 days old. The type of item is in Col. I and the age of o/s items is
    > Col.
    > V.
    > I've tried to use the sumproduct formula but I'm not getting the right
    > totals.
    > Help is greatly appreciated.
    >




  3. #3
    Aladin Akyurek
    Guest

    Re: Count of items using multiple criteria

    =SUMPRODUCT(--(TypeRange=Type),--(AgeRange>=40))

    The ranges cannot be whole columns like I:I in this type of formulas.

    mbparks wrote:
    > I am working on a spreadsheet that tracks when an item is requested, the type
    > of request it is, when it returns and the age of the outstanding items.
    > I have used formulas to automatically enter the type of item being requested
    > and the age of the outstanding items.
    > What I want to do is count the number of outstanding item#1s that are over
    > 40 days old. The type of item is in Col. I and the age of o/s items is Col.
    > V.
    > I've tried to use the sumproduct formula but I'm not getting the right totals.
    > Help is greatly appreciated.
    >


  4. #4
    Aladin Akyurek
    Guest

    Re: Count of items using multiple criteria

    =SUMPRODUCT(--(TypeRange=Type),--(AgeRange>=40))

    The ranges cannot be whole columns like I:I in this type of formulas.

    mbparks wrote:
    > I am working on a spreadsheet that tracks when an item is requested, the type
    > of request it is, when it returns and the age of the outstanding items.
    > I have used formulas to automatically enter the type of item being requested
    > and the age of the outstanding items.
    > What I want to do is count the number of outstanding item#1s that are over
    > 40 days old. The type of item is in Col. I and the age of o/s items is Col.
    > V.
    > I've tried to use the sumproduct formula but I'm not getting the right totals.
    > Help is greatly appreciated.
    >


  5. #5
    mbparks
    Guest

    Re: Count of items using multiple criteria

    This is giving me the total number of each item.
    The items are called 859 and 181.
    I should also metion that the outstanding column will only have a number if
    the item is outstanding (there is no date in the returned column). If it has
    returned I have set the formula to leave the cell blank.

    "KL" wrote:

    > Hi there,
    >
    > The way you describe it the following formula should work, I guess:
    >
    > =SUMPRODUCT((I2:I100=1)*(V2:V100>40))
    >
    > unless any of your data (type or age) are in text format of course.
    >
    > Regards,
    > KL
    >
    > "mbparks" <[email protected]> wrote in message
    > news:[email protected]...
    > >I am working on a spreadsheet that tracks when an item is requested, the
    > >type
    > > of request it is, when it returns and the age of the outstanding items.
    > > I have used formulas to automatically enter the type of item being
    > > requested
    > > and the age of the outstanding items.
    > > What I want to do is count the number of outstanding item#1s that are over
    > > 40 days old. The type of item is in Col. I and the age of o/s items is
    > > Col.
    > > V.
    > > I've tried to use the sumproduct formula but I'm not getting the right
    > > totals.
    > > Help is greatly appreciated.
    > >

    >
    >
    >


  6. #6
    KL
    Guest

    Re: Count of items using multiple criteria

    Then try:

    =SUMPRODUCT((I2:I100=181)*(V2:V100<>""))

    or

    =SUMPRODUCT(--(I2:I100=181),--(V2:V100<>""))
    as per Aladin's post.

    Regards,
    KL

    "mbparks" <[email protected]> wrote in message
    news:[email protected]...
    > This is giving me the total number of each item.
    > The items are called 859 and 181.
    > I should also metion that the outstanding column will only have a number
    > if
    > the item is outstanding (there is no date in the returned column). If it
    > has
    > returned I have set the formula to leave the cell blank.
    >
    > "KL" wrote:
    >
    >> Hi there,
    >>
    >> The way you describe it the following formula should work, I guess:
    >>
    >> =SUMPRODUCT((I2:I100=1)*(V2:V100>40))
    >>
    >> unless any of your data (type or age) are in text format of course.
    >>
    >> Regards,
    >> KL
    >>
    >> "mbparks" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >I am working on a spreadsheet that tracks when an item is requested, the
    >> >type
    >> > of request it is, when it returns and the age of the outstanding items.
    >> > I have used formulas to automatically enter the type of item being
    >> > requested
    >> > and the age of the outstanding items.
    >> > What I want to do is count the number of outstanding item#1s that are
    >> > over
    >> > 40 days old. The type of item is in Col. I and the age of o/s items is
    >> > Col.
    >> > V.
    >> > I've tried to use the sumproduct formula but I'm not getting the right
    >> > totals.
    >> > Help is greatly appreciated.
    >> >

    >>
    >>
    >>




  7. #7
    KL
    Guest

    Re: Count of items using multiple criteria

    Having re-read both of your posts I guess you may need this:

    =SUMPRODUCT((I2:I100=181)*(V2:V100<>"")*(V2:V100>40))

    or

    =SUMPRODUCT(--(I2:I100=181),--(V2:V100<>""),--(V2:V100>40))

    Regards,
    KL

    ------------
    "KL" <[email protected]> wrote in message
    news:[email protected]...
    > Then try:
    >
    > =SUMPRODUCT((I2:I100=181)*(V2:V100<>""))
    >
    > or
    >
    > =SUMPRODUCT(--(I2:I100=181),--(V2:V100<>""))
    > as per Aladin's post.
    >
    > Regards,
    > KL
    >
    > "mbparks" <[email protected]> wrote in message
    > news:[email protected]...
    >> This is giving me the total number of each item.
    >> The items are called 859 and 181.
    >> I should also metion that the outstanding column will only have a number
    >> if
    >> the item is outstanding (there is no date in the returned column). If it
    >> has
    >> returned I have set the formula to leave the cell blank.
    >>
    >> "KL" wrote:
    >>
    >>> Hi there,
    >>>
    >>> The way you describe it the following formula should work, I guess:
    >>>
    >>> =SUMPRODUCT((I2:I100=1)*(V2:V100>40))
    >>>
    >>> unless any of your data (type or age) are in text format of course.
    >>>
    >>> Regards,
    >>> KL
    >>>
    >>> "mbparks" <[email protected]> wrote in message
    >>> news:[email protected]...
    >>> >I am working on a spreadsheet that tracks when an item is requested,
    >>> >the
    >>> >type
    >>> > of request it is, when it returns and the age of the outstanding
    >>> > items.
    >>> > I have used formulas to automatically enter the type of item being
    >>> > requested
    >>> > and the age of the outstanding items.
    >>> > What I want to do is count the number of outstanding item#1s that are
    >>> > over
    >>> > 40 days old. The type of item is in Col. I and the age of o/s items
    >>> > is
    >>> > Col.
    >>> > V.
    >>> > I've tried to use the sumproduct formula but I'm not getting the right
    >>> > totals.
    >>> > Help is greatly appreciated.
    >>> >
    >>>
    >>>
    >>>

    >
    >




  8. #8
    mbparks
    Guest

    RE: Count of items using multiple criteria

    I got it! I had to use 3 arrays (I2:i100=181, v2:v100<>"" and v2:v100>40)
    and it gave me the right totals.
    I can never thank you enough. This has been driving me crazy for weeks now.
    Thank you!

    "mbparks" wrote:

    > I am working on a spreadsheet that tracks when an item is requested, the type
    > of request it is, when it returns and the age of the outstanding items.
    > I have used formulas to automatically enter the type of item being requested
    > and the age of the outstanding items.
    > What I want to do is count the number of outstanding item#1s that are over
    > 40 days old. The type of item is in Col. I and the age of o/s items is Col.
    > V.
    > I've tried to use the sumproduct formula but I'm not getting the right totals.
    > Help is greatly appreciated.
    >


+ 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