# SUMIFS MULTIPLE Criteria including between two numbers

Copper Contributor

# 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 Number Fund Program Cost Account Related Appropriation Number Calc Amount Appropriation Year AP Balance Type 1 13001 7800 92465 13001 \$73,635.88 18 01 13002 7800 92466 13002 \$74,232.12 18 01 13001 0735 91735 13001 \$248,991.93 18 01 13002 0735 94522 13002 \$251,008.07 18 01 13001 7800 92465 13001 \$73,635.88 18 11 13002 7800 92466 13002 \$74,232.12 18 11 13001 0735 91735 13001 \$248,991.93 18 11 13002 0735 94522 13002 \$251,008.07 18 11 13001 0001 91007 13001 \$1,593.29 18 16 13001 0001 91007 13001 \$216.49 18 16 13001 0001 91007 13001 \$279.25 18 16 13001 0001 91007 13001 \$85.58 18 16 00000 7033 99902 00000 \$2.10 18 12

8 Replies

# Re: SUMIFS MULTIPLE Criteria including between two numbers

You haven't told us what the problem with the formula is.

# Re: SUMIFS MULTIPLE Criteria including between two numbers

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"),

# Re: SUMIFS MULTIPLE Criteria including between two numbers

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.

# Re: SUMIFS MULTIPLE Criteria including between two numbers

@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.

# Re: SUMIFS MULTIPLE Criteria including between two numbers

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).

# Re: SUMIFS MULTIPLE Criteria including between two numbers

@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.

# Re: SUMIFS MULTIPLE Criteria including between two numbers

If you wish you can send me an anonymized file that demonstrates the problem.

# Re: SUMIFS MULTIPLE Criteria including between two numbers

@HansVogelaar thanks so much for your help with this issue. We are re-evaluating the data to be sure that it is not corrupt. I will reach out if needed in the future.

Humbly,
Regina