Forum Discussion

ReginaAnn's avatar
ReginaAnn
Copper Contributor
Jul 22, 2024

SUMIFS MULTIPLE Criteria including between two numbers

Hello,

I could really use some help with the formula below.  I believer this issue is the last part of the formula that want a number between 0 and 35.  I hope someone can assist.

 

=SUM(SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "91735", HX!P:P, "13001",HX!EY:EY, ">0", HX!EY:EY, "<35"),
SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "94522", HX!P:P, "13002",HX!EY:EY, ">0", HX!EY:EY, "<35"),
SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "92502", HX!P:P, "37435",HX!EY:EY, ">0", HX!EY:EY, "<35"),
SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "92504", HX!P:P, "3002",HX!EY:EY, ">0", HX!EY:EY, "<35"),
SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "92531", HX!P:P, "58001",HX!EY:EY, ">0", HX!EY:EY, "<35"),
SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "92534", HX!P:P, "3002",HX!EY:EY, ">0", HX!EY:EY, "<35"),
SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "92504", HX!P:P, "37435",HX!EY:EY, ">0", HX!EY:EY, "<35"),
SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "92534", HX!P:P, "58001",HX!EY:EY, ">0", HX!EY:EY, "<35"))
 
Here is a sample of the data:
Appropriation NumberFundProgram Cost AccountRelated Appropriation NumberCalc AmountAppropriation YearAP Balance Type 1
1300178009246513001$73,635.88 1801
1300278009246613002$74,232.12 1801
1300107359173513001$248,991.93 1801
1300207359452213002$251,008.07 1801
1300178009246513001$73,635.88 1811
1300278009246613002$74,232.12 1811
1300107359173513001$248,991.93 1811
1300207359452213002$251,008.07 1811
1300100019100713001$1,593.29 1816
1300100019100713001$216.49 1816
1300100019100713001$279.25 1816
1300100019100713001$85.58 1816
0000070339990200000$2.10 18

12

 
Thank you for any help you can provide.
Formulas and Functions

8 Replies

  • ReginaAnn's avatar
    ReginaAnn
    Copper Contributor
    Jul 22, 2024
    HansVogelaar It would like let me attach the file here and so I've sent it to you in a private message. Thank you for your time.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 22, 2024

      ReginaAnn 

      Thanks. When I rename the sheet in your sample workbook to HX, and enter the formula in another sheet, the result is not 0 but 1,000,000. This is because rows 4, 5, 8 and 9 meet the criteria (you forgot to mark row 5).

      • ReginaAnn's avatar
        ReginaAnn
        Copper Contributor
        Jul 23, 2024
        HansVogelaar, yes I see that it does work on the small data set that I sent to you. Unfortunately it is still not working on my large data set. Thank you for your help.
  • ReginaAnn's avatar
    ReginaAnn
    Copper Contributor
    Jul 22, 2024

    Hi HansVogelaar, I probably did not say it clearly. I believe the issue is the last part of the formula where part is the calculation is between 0 and 35, HX!P:P, "13001",HX!EY:EY, ">0", HX!EY:EY, "<35"). The formula is currently returning a zero in each instance and this is incorrect. Thanks, =SUM(SUMIFS(HX!BB:BB, HX!DU:DU, "18", HX!Q:Q, "0735", HX!R:R, "91735", HX!P:P, "13001",HX!EY:EY, ">0", HX!EY:EY, "<35"),

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 22, 2024

      ReginaAnn 

      Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

Resources