Someone else may correct me, amaka, but I do think it's obvious. Your SUMIF function is looking at the values between rows 12 and 23875; but after you sort the data, I suppose that there are a different set of values in that range. Perhaps one of the values that was in row 33 is now in row 8, so it's outside the summed range; and some value that was in row 35133 is now in row 8305, so it's now inside that range.

I can't see your data so I can't be sure. But that's the first thing I think of.

thanks a lot for responding however, I am attempting to upload the excel sheet to demonstrate the issue better. The filter is a row 12 so, the sorted values never go above 12. Also, the last row with any data in is is 738 so there is no way the data is outside the range. please see the attached file.

Attached Files

sumif but has tobe sorted.xlsx (55.0 KB, 11 views)

I dunno, amaka, maybe you accidentally uploaded the wrong workbook. The one I'm looking at doesn't have any data past row 51:And the last range in the function has the #REF error. If this is the right workbook, maybe you deleted a bunch of rows to make the example simpler?

Hello

This problem is literally driving me nuts. Please see the attached excel sheet. When The raw data sheet of all product is sorted by product I get a sunif in Jul on the premium sheet of 85200 which is wrong. When the price sheet is sorted by Product, I get a sumif value of 107800, the correct one.

How can I keep the sumif function correct regardless of the sorting in the original sheet.

Attached Files

excel question.xlsx (112.0 KB, 12 views)