+ Reply to Thread
Results 1 to 5 of 5

=SUM(countifs with two set of multiple data

  1. #1
    Registered User
    Join Date
    10-24-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    13

    =SUM(countifs with two set of multiple data

    Hello,

    I am currently trying to get this formula to work properly =SUM(COUNTIFS('PC'!$R:$R,{"H","D","L","M","O","P","R","V"},'PC'!$P:$P,{"C1*","C2*","C3*","B1*"}))

    It is currently returning a value of 2 which i know is incorrect because the below formula returns a 3 for M (D178& in the below formula targets a cell containing M) so the above formula should return either a 3 or 4 all data considered.

    =SUM(COUNTIFS('PC!$N:$N,"*"&$D178&"*",'PC!$P:$P,{"C1*","C2*","C3*","B1



    Any ideas on if Using SUM(countifs can be used like this? i would have expected it to either return the correct answer or error.


    I can’t attach the actually spread sheet due to data protection and would take too long to edit as i have with the above formula.

    Any help will be Greatly Appreciated

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

    Re: =SUM(countifs with two set of multiple data

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: =SUM(countifs with two set of multiple data

    Are you trying to reference cells in that last section ("C1*","C2*","C3*","B1*") or would the text actually start with either C1,C2,C3 or B1?

    So what you want to count is all instances where Column R contains one of the first array values AND (in the same row), Column P begins with either C1,C2,C3 or B1?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    10-24-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    13

    Re: =SUM(countifs with two set of multiple data

    I have attatched a mock spred sheet with all other data cut out and the origonal data modified to not reveal its origional contents. i hope this should show what i am after.
    Attached Files Attached Files

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

    Re: =SUM(countifs with two set of multiple data

    Hi,

    When using COUNTIFS in such a way as to calculate all possible permutations of two sets of criteria, you must ensure that the two arrays being passed as the criteria are orthogonal, i.e. one a vertical array, the other a horizontal array. In English versions of Excel the comma and semicolon represent the separator for horizontal and vertical arrays respectively.

    In a nutshell, you simply have to transpose one of your criteria arrays.

    Either:

    =SUM(COUNTIFS(PC!$Q:$Q,{"H","D","L","M","O","P","R","V"},PC!P:P,{"C1*";"C2*";"C3*";"B1A"}))

    or:

    =SUM(COUNTIFS(PC!$Q:$Q,{"H";"D";"L";"M";"O";"P";"R";"V"},PC!P:P,{"C1*","C2*","C3*","B1A"}))

    Your current formula, i.e.:

    =SUM(COUNTIFS(PC!$Q:$Q,{"H","D","L","M","O","P","R","V"},PC!P:P,{"C1*","C2*","C3*","B1A"}))

    by not doing this, effectively calculates over just the following 8 permutations (in which each element in the first criteria array is paired with the corresponding element from the second criteria array), instead of the required 32.

    =COUNTIFS(PC!$Q:$Q,"H",PC!P:P,"C1*")
    =COUNTIFS(PC!$Q:$Q,"D",PC!P:P,"C2*")
    =COUNTIFS(PC!$Q:$Q,"L",PC!P:P,"C3*")
    =COUNTIFS(PC!$Q:$Q,"M",PC!P:P,"B1A")
    =COUNTIFS(PC!$Q:$Q,"O",PC!P:P,NA())
    =COUNTIFS(PC!$Q:$Q,"P",PC!P:P,NA())
    =COUNTIFS(PC!$Q:$Q,"R",PC!P:P,NA())
    =COUNTIFS(PC!$Q:$Q,"V",PC!P:P,NA())


    Note the NA functions in the last four of the above. Effectively, any #N/A errors in column P would actually be picked up by your current formula for column Q entries of "O", "P", "R" and "V".

    https://excelxor.com/2014/09/28/coun...iteria_ranges/

    Regards
    Last edited by XOR LX; 06-04-2020 at 03:57 PM.
    Click * below if this answer helped

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

+ 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 Function for Multiple Data in the Same Cell
    By doubleuson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-31-2018, 09:41 AM
  2. Countifs with multiple criteria and data range
    By Pritirus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-21-2016, 01:18 PM
  3. [SOLVED] SUMPRODUCT? COUNTIFS? Extract data with multiple critea
    By 1losthuman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-25-2015, 06:51 PM
  4. Countifs using multiple rows to obtain data
    By LeapingLizard in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-05-2015, 04:57 PM
  5. Using CountIfS function on multiple lines of data...
    By Buratti in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2014, 03:09 PM
  6. COUNTIFS OR...multiple countifs without duplication in the numbers
    By HooligaD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2012, 09:53 AM
  7. Replies: 0
    Last Post: 07-27-2011, 01:00 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