+ Reply to Thread
Results 1 to 8 of 8

Need help on COUNTIFS, INDEX and MATCH

  1. 07-16-2023, 10:55 PM #1
    Monteverdi
    Monteverdi is offline
    Registered User
    Join Date
    07-16-2023
    Location
    Indonesia
    MS-Off Ver
    Google Spreadsheet
    Posts
    4

    Need help on COUNTIFS, INDEX and MATCH

    Hi, I have problem with COUNTIFS, INDEX and MATCH.

    I attached the example tables.

    I have problem if I want to count cell which has value (is not empty, nor zero nor " ") in non-contiguous columns.

    If counting in consecutive columns, I have no problem, like the example below:

    For example I want to count cells which has value with criterias "Citra Ayunda" (cell B5) and "SE" (cell C3) in range B:W I can use this formula: =COUNTIFS(Summary!$B$3:$W$3,C$3,INDEX(Summary!$B:$W,MATCH($B5,Summary!$B:$B,0),0),">0") and it will return value = 5.

    The problem is if I want to count cell with values in L:W, how do I use COUNTIFS, INDEX and MATCH formula? (And it must return value = 2)

    Thanks in advance!
    Attached Files Attached Files
    Last edited by Monteverdi; 07-17-2023 at 12:26 AM.
    Reply With QuoteRegister To Reply
  2. 07-16-2023, 11:10 PM #2
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,004

    Re: Need help on COUNTIFS, INDEX and MATCH

    =SUM(IFERROR(($L$3:$W3="SE")*(--$L5:$W5>0),0))

    committed with Ctrl+Shift+Enter.
    Reply With QuoteRegister To Reply
  3. 07-16-2023, 11:22 PM #3
    Monteverdi
    Monteverdi is offline
    Registered User
    Join Date
    07-16-2023
    Location
    Indonesia
    MS-Off Ver
    Google Spreadsheet
    Posts
    4

    Re: Need help on COUNTIFS, INDEX and MATCH

    Thanks for help, but unfortunately it doesn't work. It returns value = 0. (Should be 2).
    Reply With QuoteRegister To Reply
  4. 07-16-2023, 11:25 PM #4
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,004

    Re: Need help on COUNTIFS, INDEX and MATCH

    Have you committed with Ctrl+Shift+Enter?

    See attached file in AT5.

    PS. If you use google sheet, try
    =ARRAYFORMULA(SUM(IFERROR(($L$3:$W3="SE")*(--$L5:$W5>0),0)))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-17-2023, 12:04 AM #5
    Monteverdi
    Monteverdi is offline
    Registered User
    Join Date
    07-16-2023
    Location
    Indonesia
    MS-Off Ver
    Google Spreadsheet
    Posts
    4

    Re: Need help on COUNTIFS, INDEX and MATCH

    Yes, actually I'm using it on Google Sheet. And it works now. And it works as well in MS Excel after I committed with CTRL+SHIFT+ENTER. Thank you.

    I have another question, in MS Excel, why should we do CTRL+SHIFT+ENTER? Is it different when compared to ENTER?
    Reply With QuoteRegister To Reply
  6. 07-17-2023, 12:11 AM #6
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,004

    Re: Need help on COUNTIFS, INDEX and MATCH

    Maybe, you can see MS's explanation.

    https://support.microsoft.com/en-au/...2-ecfd5caa57c7
    Reply With QuoteRegister To Reply
  7. 07-17-2023, 12:26 AM #7
    Monteverdi
    Monteverdi is offline
    Registered User
    Join Date
    07-16-2023
    Location
    Indonesia
    MS-Off Ver
    Google Spreadsheet
    Posts
    4

    Re: Need help on COUNTIFS, INDEX and MATCH

    Got it. Thank you windknife!
    Reply With QuoteRegister To Reply
  8. 07-17-2023, 12:27 AM #8
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,004

    Re: Need help on COUNTIFS, INDEX and MATCH

    You are welcome.
    -----------------------------------
    If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.
    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. [SOLVED] How to use Countifs with Index/Mode/Match
    By Magaletta in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-29-2022, 06:24 AM
  2. COUNTIFS with INDEX MATCH ?
    By mameri1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-13-2021, 10:22 PM
  3. [SOLVED] Countifs with index and Match Function
    By RAMESHB in forum Excel General
    Replies: 5
    Last Post: 08-29-2019, 03:25 AM
  4. Countifs with index match
    By Paul.Thompson in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-12-2019, 09:41 AM
  5. using countifs with index and match
    By cvandy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-23-2018, 02:43 AM
  6. INDEX, MATCH and COUNTIFS
    By 1953CAG in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-18-2017, 04:57 AM
  7. Countifs Index Match
    By Keelin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-16-2014, 08:38 AM

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