+ Reply to Thread
Results 1 to 4 of 4

Count If multiple functions

  1. #1
    Registered User
    Join Date
    11-21-2019
    Location
    australia
    MS-Off Ver
    2016
    Posts
    17

    Count If multiple functions

    Help!
    I have a spreadsheet with resolved (Status Col D) and a date (Status Col E). On another sheet I want to count the different resolved types only if they are type A and B and roll the dates in Status Column E up to the months in Assess Col A

    I have tried the following;

    When I enter COUNTIFS(Status!$D$4:$D$34,"A",Status!D4:D34,"B",Status!$E$4:$E$34,">="&Assess!A6,Status!$E$4:$E$34,"<="&EOMONTH(Assess!A6,0)) it returns 0 but it should result in 2

    COUNTIFS(Status!$D$4:$D$34,"A",Status!$E$4:$E$34,">="&Assess!A7,Status!$E$4:$E$34,"<="&EOMONTH(Assess!A7,0)) it returns the correct value of 1

    What is the best formula to enter to count when resolved = A and B and roll up all instances of A and B into the relevant month
    Thanks
    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
    16,168

    Re: Count If multiple functions

    Hi Aggaire,

    I'd use a pivot table with filters and counts. No formula needed? See the attached. Time to learn Pivot Tables?

    PT with Filters Columns and Count Ben Test.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Count If multiple functions

    You said "A and B" but from a logic standpoint you mean "A or B". None of the cells can have both A and B, which is why you get a 0 result. If you want to count all the cells that have A or B you can use an array:

    =SUM(COUNTIFS(Status!$D$4:$D$34,{"A","B"},Status!$E$4:$E$34,">="&Assess!A7,Status!$E$4:$E$34,"<="&EOMONTH(Assess!A7,0)))

    This will cause COUNTIFS to return an array of values, so you need to use SUM to add those values.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    Re: Count If multiple functions

    copy down


    G6=IF(Assess!$A6<>"",SUMPRODUCT((MONTH(Status!$E$4:$E$34)=MONTH(Assess!$A6))*(ISNUMBER(MATCH(Status!$D$4:$D$34,Status!$D$4:$D$5,0)))*(YEAR(Status!$E$4:$E$34)=YEAR(Assess!$A6))),"")

+ 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: 6
    Last Post: 03-17-2015, 01:35 AM
  2. Count Unique Entries with multiple If functions
    By paul_lowry in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2015, 11:29 AM
  3. Count if multiple functions are true?
    By eoindub in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-10-2014, 11:51 AM
  4. [SOLVED] Count how often multiple number values occur by using functions
    By joefitness in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2014, 12:21 AM
  5. Combining multiple functions>lookup/sum functions
    By mush106 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2013, 07:47 AM
  6. Excel 2007 : Multiple Conditional Count Functions
    By J Morrow in forum Excel General
    Replies: 2
    Last Post: 05-17-2011, 03:00 AM
  7. Replies: 2
    Last Post: 05-11-2010, 12:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1