Share via

Using multiple criteria per range in a SUM(COUNTIFS formula & using a reference cell to detail the multiple criteria

Anonymous
2023-12-19T15:49:51+00:00

Hi, I need some help using SUM(COUNTIFS(. My question is regarding how to count multiple criteria per range, i do this regularly. The first multiple criteria per range i use {} with commas to separate criteria. for the 2nd multiple criteria per range, i use {} with a semicolon. I am having issue getting a third multiple criteria per range work...using the same as the 2nd one. Second question, is can i use a reference cell to do the {} info so i don't need to fix every time i have an adjustment?

=SUM(COUNTIFS('AC-RC invol percent data set'!$E:$E,">="&MonthSkilz!C$6,'AC-RC invol percent data set'!$E:$E,"<="&MonthSkilz!C$7,'AC-RC invol percent data set'!$D:$D,{"D","G","FN","FD"},'AC-RC invol percent data set'!$F:$F,"R-IV",'AC-RC invol percent data set'!$H:$H,{"Y";"N"},'AC-RC invol percent data set'!$C:$C,{"NE*";"NN-6374*";"AR*";"FG*"}))

For the last argument I want to put the {"NE*";"NN-6374*";"AR*";"FG*"} in another cell, as i use a similar formula in many rows, and want to change it once to the reference cell so whenever i need to adjust the spreadsheet, i can change it in a single cell to effect all my formulas...

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-12-19T18:05:17+00:00

    I think this is limitation in countif function. You may need to use other function to get the result.

    1 person found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-12-19T17:56:20+00:00

    Thanks for the replay again. Unfortunately, No it is not what I am looking for.

    I just want to COUNT whenever my ranges and criteria's that i call out are present. I know for the first multiple criteria range i use {} with commas separating and the 2nd i use {} with semicolons didn't know what i need to use for the 3rd or more criteria's and if i just keep using {} with semicolons.

    I was hoping I could get an insight on that one... if there is a limit to how many multiple criteria i can search for ranges...

    0 comments
  2. Anonymous
    2023-12-19T17:42:57+00:00

    Is this one you want?

    =SUM(COUNTIF(A1:A2,A5:A13)*COUNTIF(B1:B2,B5:B13)*COUNTIF(C1:C2,C5:C13)*(ISNUMBER(SEARCH(D1,D5:D13))+ISNUMBER(SEARCH(D2,D5:D13))))

    0 comments
  3. Anonymous
    2023-12-19T17:25:47+00:00

    I am not permitted to share the sheet, trying to replicate it with a much simplier sheet didn't return any errors. for my sheet here are two formulas, only thing that changes is the last Criteria, and

    =SUM(COUNTIFS('AC-RC invol percent data set'!$E:$E,">="&MonthSkilz!C$6,'AC-RC invol percent data set'!$E:$E,"<="&MonthSkilz!C$7,'AC-RC invol percent data set'!$D:$D,{"D","G","FN","FD"},'AC-RC invol percent data set'!$F:$F,"R-IV",'AC-RC invol percent data set'!$H:$H,{"Y";"N"},'AC-RC invol percent data set'!$C:$C,"NE*"))

    This returns '18'

    =SUM(COUNTIFS('AC-RC invol percent data set'!$E:$E,">="&MonthSkilz!C$6,'AC-RC invol percent data set'!$E:$E,"<="&MonthSkilz!C$7,'AC-RC invol percent data set'!$D:$D,{"D","G","FN","FD"},'AC-RC invol percent data set'!$F:$F,"R-IV",'AC-RC invol percent data set'!$H:$H,{"Y";"N"},'AC-RC invol percent data set'!$C:$C,{"NE*";"NN-6374*";"AR*";"FG*"}))

    This returns '0', when i know for sure it should be 18 or greater... This leads me to think that when i get to the 3rd multiple criteria {} it is creating a problem...

    I made this simple table and formula to test, and it seems it should work just fine...

    =SUM(COUNTIFS(A5:A13,{"A","C"},B5:B13,{"Y";"N"},C5:C13,{"AA";"AB"},D5:D13,{"TE*";"M*"}))

    0 comments
  4. Anonymous
    2023-12-19T16:28:37+00:00

    Could you share us a test file to test formulas? Then we may have a try.

    For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

    0 comments