# SumIf Formula Not Adhering to Criteria

1. ## SumIf Formula Not Adhering to Criteria

Hello all and thank you to anyone and everyone that can help me out.

I have a simple SUMIF formula, but the result seemingly returns the sum amount that includes a number that does not meet the criteria in the formula.

=SUMIF(\$A\$1:\$H\$18,J20,\$H\$1:\$H\$18)

Cell J20 is "OT" but the formula seems to include the sum amount only for [H7(Reg):H8(OT)] yet excludes all others... I'm confused on the reasoning???

2. ## Re: SumIf Formula Not Adhering to Criteria

The formula works correctly. There are hidden rows!

3. ## Re: SumIf Formula Not Adhering to Criteria

With
=SUMIF(\$A\$1:\$H\$18,J20,\$H\$1:\$H\$18)
You are using 2 ranges different in size: one has 8 columns but other has 1 column only.
SUMIF works with single range (BOTH vertical OR horizontal range)

so, try:

=SUMIF(\$A\$1:\$A\$18,J20,\$H\$1:\$H\$18)

4. ## Re: SumIf Formula Not Adhering to Criteria

Originally Posted by phatdear
The formula works correctly. There are hidden rows!
Thank you checking it out,
The original spreadsheet is a template that is going to include those currently for the time being; although, the hidden rows have no values within them which I assume should not make any difference.

5. ## Re: SumIf Formula Not Adhering to Criteria

I tried the formula and it resulted with the same amount

6. ## Re: SumIf Formula Not Adhering to Criteria

Did you see post #2? What result are you expecting?

7. ## Re: SumIf Formula Not Adhering to Criteria

=SUMIF(\$A\$1:\$A\$18,J19,\$H\$1:\$H\$18)

returns \$1,676.80

Why do you expect a different answer, when that is the correct answer? You are either mistaken, or have failed to tell us something!!

8. ## Re: SumIf Formula Not Adhering to Criteria

Just to point out to you again, this is from the the original file that you provided.
There are hidden rows (13-18) and there is value in one cell which is resulted from another formula.

js-MDNmg-Wg1.png

9. ## Re: SumIf Formula Not Adhering to Criteria

There is no value in the hidden rows that makes a difference - both Reg values are 0.

There are currently 1 users browsing this thread. (0 members and 1 guests)