Using absolute value for criteria range in Sumifs

Formula:

Please Login or Register  to view this content.

could become:

Formula:

Please Login or Register  to view this content.

However, although you can use full column references in SUMPRODUCT in Excel 2007 and above, you should not do so as it will affect the performance of the spreadsheet. In that respect, it would be better to use Dynamic Named Ranges or Structured Tables for the data.

Best bet would be to post a sample workbook.

Regards, TMS

never tried what you are doing but maybe you need the ABS in front of the sumifs, have you tried that?
and this part would seem to give you a circular reference, no? AD:AD,AD15

Hey Trevor, aren't J:J,J15 and AD:AD,AD15 circular references? (just curious)

The alternative of sumifs in this case would be the Sumproduct formula like this......

Please Login or Register  to view this content.

****Referencing whole column (like AD:AD) is not a good idea.

@sktneer:

The correct Sumproduct formula would be like this......

Meaning what? My suggested formula is incorrect? It works, as is, but is noticeably slow due to the full column references.

@Sambo kid: no, it's not a circular reference. It's just using one entry in the range for the comparison. Strange, maybe, as clearly at least one entry is equal to the value being tested for .. that is, itself.

@HcLax: Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Originally Posted by TMS

@sktneer:

Meaning what? My suggested formula is incorrect?

Oh not really. It's just different approach of writing it.
***Edited my last post.

@sktneer:

I am conscious that I tend to use a double negative when it is not necessary (for the column being summed) and I generally will put the summed range in brackets even though that isn't strictly necessary either. Force of habit, and I do like the symmetry Your version is probably more efficient.

Regards, TMS