+ Reply to Thread
Results 1 to 4 of 4

Count using Multiple Criteria:find the number of customers

  1. #1
    Registered User
    Join Date
    04-05-2011
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    2

    Count using Multiple Criteria:find the number of customers

    Hello,

    I'm trying to find the number of customers within a year that purchase only parts, service, or parts & service from a pivot table.

    The criteria will contain two criterias. Count all records if a customer does only parts sales. Count all records if a customer does both parts and service.

    Is this possible? I've been searching google the past hours.

    Any help much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,858

    Re: Help: Count using Multiple Critera

    Hi Jim and welcome to the forum,

    You could if you set your data up correctly. Your blanks on rows keeps you from accomplishing your goal.
    See the attached where I've changed this. It may not be exactly what you need but you will see why there is a problem.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    04-05-2011
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Help: Count using Multiple Critera

    Hi Marvin,

    Thank you for taking a look. So my goal is not to find the the total sales within a year, but to find the number of customers that we sold too in a year and then split them into 3 categories: Parts, Service, & Both.

    Goal:
    What I was thinking if the formula was possible.

    =countif(A11:A25,and(sdcust>1,SDGAC2="parts",SDGAC2<>"service",year(sdtrdt)=2007))
    This formula would sum any customer number who does parts sales and does not do any service sales for the year 2007.

    I would then redefine the formula for service customer as SDGAC2="service", SDGAC2<>"parts"

    I hope this clearly defines the problem

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,858

    Re: Help: Count using Multiple Critera

    Hi Jim,

    You will still need to copy the data to the blank rows. You can the drag the customer down to either the row or put them as a filter. This will allow you to see the number of times that customer had bought Parts, Service or.. which could be on the column heads.

+ 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