+ Reply to Thread
Results 1 to 4 of 4

Count ifs multiple criteria & average ifs multiple criteria

  1. #1
    Registered User
    Join Date
    02-04-2021
    Location
    HA
    MS-Off Ver
    2016
    Posts
    14

    Count ifs multiple criteria & average ifs multiple criteria

    Hi All,

    I am trying to solve this scenario.

    Column A: Cars (Toyota, Chevy, GMC, etc.)
    Column B: Salesperson (John, Lance, Kathy, Kassidy)

    Im trying to do a countifs function to get the total amount of any two specific salesman. For example, i want to know how many toyotas both John and lance sold. I tried =COUNTIF(A1:A10, "=Toyota", B1:B10, "=John", A1:A10, "Lance") but it comes up with "0" im guessing because the three criterias are not being met. I know i can do two separate countif functions and add them but i wanted to see if it could be done in one.

    Also,

    adding to the above statement. Column C: Duration in days (10, 20, 5, etc.) i am trying to also do an averageifs function for the above scenario. For example, on average how long did it take john and lance to sell those toyotas. But i keep getting an error. I am trying =AVERAGEIFS(C1:C10, A1:A10, "=Toyota",B1:B10, "=John", A1:A10, "Lance")

    Thank you for the help!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,253

    Re: Count ifs multiple criteria & average ifs multiple criteria

    Something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You might need to commit the formula with Ctrl-Shift-Enter rather than just Enter with your version of Excel.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,576

    Re: Count ifs multiple criteria & average ifs multiple criteria

    Hi,
    Try:
    =SUM(((B2:B6="John")+(B2:B6="Lance"))*(A2:A6="Toyota"))

  4. #4
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    682

    Re: Count ifs multiple criteria & average ifs multiple criteria

    You can use COUNTIFS as TMS did if you want. You get kind of the same result using IF like this though. And it extends to getting the average more easily.

    Cars in A2:A4
    People in B2:B4
    Durations in C2:C4

    Car to search (eg Toyota) in F1
    People to search (eg John and Lance) in F2:F3

    Count (in F5):
    =SUM(IF(($A$2:$A$4=$F$1)*($B$2:$B$4=TRANSPOSE($F$2:$F$3)),1,0))

    Average:
    =SUM(IF(($A$2:$A$4=$F$1)*($B$2:$B$4=TRANSPOSE($F$2:$F$3)),$C$2:$C$4,0))/$F$5

    See attached mockup.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 14
    Last Post: 06-07-2021, 11:31 AM
  2. Replies: 7
    Last Post: 04-07-2021, 06:04 PM
  3. Replies: 2
    Last Post: 08-16-2020, 09:46 PM
  4. Count with multiple criteria (Date, Time and Other Criteria)
    By Naren_Kumar in forum Excel General
    Replies: 6
    Last Post: 06-25-2018, 05:53 PM
  5. Average based on criteria and multiple criteria
    By batchjb69 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2016, 04:30 AM
  6. Count statement wtih multiple criteria only showing results for one criteria
    By uhlabomber in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2013, 02:47 PM
  7. Return average and count from multiple criteria in a list or array of data
    By robcosta in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-22-2008, 03:18 AM

Tags for this Thread

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