+ Reply to Thread
Results 1 to 6 of 6

Countifs with cells that contain multiple data

  1. #1
    Registered User
    Join Date
    08-25-2021
    Location
    Horsham
    MS-Off Ver
    365
    Posts
    2

    Countifs with cells that contain multiple data

    Hi

    Hope someone can help as this is sadly beyond my Excel know how.

    I have a sheet that I need to count occurrences from. I can handle cells with single data but I need to have 1 cell that has days of the week in. In the attached sheet cell C5 has a data validation to select days of the week which is controlled by a little VBA code. Can someone assist with the formula required to search the sheet using criteria in C5 to C9

    Any help is greatly appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: Countifs with cells that contain multiple data

    Try

    =SUM(COUNTIFS(Data!$D:$D,">="&C$8,Data!$E:$E,"<="&C$9,Data!F:F,"="&$C$7,Data!G:G,"="&$C$6,Data!H:H,{"Wed","Thu"}))

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,717

    Re: Countifs with cells that contain multiple data

    Hi,

    =SUMPRODUCT(COUNTIFS(Data!D:D,">="&C8,Data!E:E,"<="&C9,Data!F:F,C7,Data!G:G,C6,Data!H:H,FILTERXML("<a><b>"&SUBSTITUTE(C5,",","</b><b>")&"</b></a>","//b")))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: Countifs with cells that contain multiple data

    If days are in E1:E2

    E1=LEFT(C5,3)
    E2=Right)C5,3)

    =SUMPRODUCT(COUNTIFS(Data!$D:$D,">="&C$8,Data!$E:$E,"<="&C$9,Data!F:F,"="&$C$7,Data!G:G,"="&$C$6,Data!H:H,E1:E2))

    but you have a 365 solution anyway

  5. #5
    Registered User
    Join Date
    08-25-2021
    Location
    Horsham
    MS-Off Ver
    365
    Posts
    2

    Re: Countifs with cells that contain multiple data

    Amazing, thanks. Would really like to understand how that worked.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: Countifs with cells that contain multiple data

    I assume your question is for XORLX.

+ 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. [SOLVED] Countifs formula not working with multiple cells
    By Nitinkumar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-18-2019, 05:10 AM
  2. Most Efficient way of CountIfs combinations of multiple cells
    By cocacrave in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-02-2015, 12:07 AM
  3. COUNTIFS, multiple criteria and not including blank cells
    By qhoney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2014, 05:14 PM
  4. [SOLVED] CountIFS with multiple criteria to ignor blank cells
    By dbaker4020 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2013, 09:03 AM
  5. Replies: 0
    Last Post: 07-27-2011, 01:00 AM
  6. Replies: 1
    Last Post: 03-21-2011, 01:39 PM
  7. Countifs alternative on 2003 with multiple cells and worksheets
    By Ex0dus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-18-2009, 10:44 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