+ Reply to Thread
Results 1 to 2 of 2

Counting the Occurance of a Root Number only

  1. #1
    Registered User
    Join Date
    02-14-2013
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    16

    Counting the Occurance of a Root Number only

    Hello all,

    First, have to put out there that I am a newbie to posting on a help forum - but I have always found SO much useful advice and solutions here, that I thought I'd give this problem a go. There may be a solution out there, and I'm just not searching for it properly. Anyhow - here goes...

    I am modifying a spreadsheet where I need to report on the number of orders that are received. However, for reasons I can't explain (the spreadsheet was set up in a way and I'm having issues changing their reasoning) - each line is an amendment of the root number. For example, an order with 4 items would be entered as follows:

    IT13-ABC-0211 (1)
    IT13-ABC-0211 (2)
    IT13-ABC-0211 (3)
    IT13-ABC-0211 (4)

    I need to report on the total number of ITEMS ordered -which is easy. But, I also need to report on the number of ORDERS received. I have a summary sheet that does a Countif of items. How do I do a count of total orders received? Basically - how do I count the occurance of the "root number" just once - keeping in mind that the root number will change everytime an order with multiple items is received?

    Alternatively - do you have a suggestion on how the order numbers could be entered and reported on to gather the same information (without creating a bunch of additonal cells in an already cumbersome spreadsheet?)

    Thanks in advance!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Counting the Occurance of a Root Number only

    =SUMPRODUCT(--(RIGHT($A$1:$A$500,3)="(1)")) would probably do it
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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