Im relatively new to excel so please excuse me if the answer is obvious. I have a sheet with several columns. On sheet 2 I have sumif reports on based on the criteria in a specific range. When that range (text) is sorted, I get an accurate result. But when I sort any other column so that the criteria range is not 'arranged' the sumif report changes. please help This is the sumif equation
=SUMIF('Price all products'!B12:B23875,"hnd",'Price all products'!BDE12:BDE23875)

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, 9 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, 10 views)

Not quite sure but I get 106800 by changing the sum range start row to 5 instead of 4 Like

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks, that seems to have fixed it!