+ Reply to Thread
Results 1 to 7 of 7

Count function based on multiple columns

  1. 08-05-2021, 01:29 PM #1
    Cardan
    Cardan is offline
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Count function based on multiple columns

    Working on a formula I thought would be easy but it is perplexing me.
    The attached workbook has 3 columns and trying to count unique occurrences in Column E (Products Delivered) based on values in columns C and D.

    I thought COUNTIFS could work but each criteria needs a value to search for. I thought SUMPRODUCT might work but it won't identify the unique value number I am looking for?

    Any advice helps. Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-05-2021, 01:53 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,903

    Re: Count function based on multiple columns

    Here is a Power Query Solution

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    		 Vendor Month Due Count
    2
    		 Acme July
    4
    3
    		 Acme August
    3
    4
    		 Bander May
    3
    5
    		 Acme May
    2
    6
    		 Acme March
    2
    Sheet: Table1 

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 08-05-2021, 02:09 PM #3
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,870

    Re: Count function based on multiple columns

    What version of Excel are you using? I suggest you update your profile to show the version.
    If it's 365 how about
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 08-05-2021, 02:25 PM #4
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    2,009

    Re: Count function based on multiple columns

    G6=SUM(IF(FREQUENCY(IF($C$4:$C$43=G4,IF(D4:D43=G5,MATCH(E4:E43,E4:E43,0))),ROW(E4:E43)-ROW(E4)+1),1))

    Control+shift+enter
    Reply With QuoteRegister To Reply
  5. 08-05-2021, 02:53 PM #5
    Cardan
    Cardan is offline
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Count function based on multiple columns

    I like it! I am using 365 and tried the UNIQUE(FILTER option but that returned the range. I don't think I have ever seen the ROWS function before. Thank you!
    Reply With QuoteRegister To Reply
  6. 08-05-2021, 02:54 PM #6
    Cardan
    Cardan is offline
    Registered User
    Join Date
    07-26-2006
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Count function based on multiple columns

    Thank you for the response! I have never used power query but given my recent question, I need to learn. Thank you for the response!
    Reply With QuoteRegister To Reply
  7. 08-05-2021, 02:58 PM #7
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,870

    Re: Count function based on multiple columns

    The formula I suggested return 4 for me. What does it return for you?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Count rows, based on two criteria across multiple columns (with blanks)
    By NV Social Worker in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2021, 06:41 PM
  2. count cells in 1 column and average based on multiple criteria (different columns)
    By olga6542 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2020, 09:46 PM
  3. [SOLVED] Count in cells based on multiple criteria in other columns
    By Copernicus19 in forum Excel General
    Replies: 2
    Last Post: 07-17-2019, 10:17 PM
  4. [SOLVED] Count the number of different values based on MULTIPLE columns
    By Tho Pham in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-15-2018, 03:44 AM
  5. Need to count rows based on multiple criteria, each across multiple columns!
    By kitfriendesq in forum Excel General
    Replies: 1
    Last Post: 02-09-2017, 05:55 PM
  6. How count occurences in multiple separated columns using countif or other function
    By mattin in forum Excel General
    Replies: 5
    Last Post: 06-06-2016, 01:08 PM
  7. [SOLVED] count and sum based on criteria in multiple columns
    By BBCline in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-14-2012, 04:08 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1