SUMIFS breaking when entering data in a non-referenced field

[Decryptix]

Good morning,

New issue I've run into while doing some QA on a calculator.

The SUMIFS formula I'm using works for one row, then not the next, sporadically... nothing but the criteria has changed. I've verified the following:

1. No extra spaces before or after
2. No references to cells outside of what needs to be referenced
3. No differentiation in formatting of cells.
4. Dragged the formula from the working row at the top, down.
5. All referenced tables/lists are formatted the same with no extra spaces/numbers.

=IF(E2="","",(SUMIFS(Table8[Rate],Table8[Grade],D2,Table8[Spec],E2,Table8[PI],O2)))

Here's a link to the sheet: New Calc - Engineers (Compatible).xlsx

It's starting to drive me a bit batty. I've re-written that same formula a dozen different ways, but even using nested IF/AND statements or Aggregate statements run into the same issue. I've looked over the lists the information is pulled from, and see no issues that would for the problem to occur.

Some information seems to cause the issue, which led me to believe it was the information itself, but when verifying the table data it is correct...

I appreciate the assistance once more!

 

[Fluff]

Which cells are wrong?

 

[Decryptix]

Which cells are wrong?

I suppose that information would be helpful, sorry about that! In the version I've uploaded, specifically P3, P10,P11,P15,P18. The New Rate col (P) should never be 0.00 unless the key information (Grade, and Spec) are empty.

The new rate col (P)is where the error occurs and I can't pin down what's causing it, as I can't get it to consistently return an error. Sometimes it will not populate a rate, other times it will work for two of the three specialties, but not the third, or vice versa.

 

[Fluff]

Thanks for that.
The problem is Col M are not whole numbers & so when col O pulls the max of M & N it isn't a whole number & so the sumifs cant find a match for O2.
Try changing col M to

=IF(C2="","",IF(YEARFRAC(C2,"31-Mar-21")-1<=0,0,ROUND((YEARFRAC(C2,"31-Mar-21")-1),0)))

 

[Decryptix]

Try changing col M to

=IF(C2="","",IF(YEARFRAC(C2,"31-Mar-21")-1<=0,0,ROUND((YEARFRAC(C2,"31-Mar-21")-1),0)))

That seems to have fixed it! One final question before I mark it as solved - should I use ROUND for the first YEARFRAC as well? Or is it because that only handles it if it's less than 0, or makes it 0 that we don't need to have it there?

Thanks again! You're an absolute ninja!

 

[Fluff]

should I use ROUND for the first YEARFRAC as well?

You could, depends on what you want/need.

 

[Decryptix]

You could, depends on what you want/need.

I'll keep that in mind, if I notice things not lining up I'll be sure to give it a go. Thanks again, I appreciate the assistance. Clearly there's a lot more I need to take the time to learn about Excel.

 

[Fluff]

Glad to help & thanks for the feedback.