SUMIF: don't understand that purpose of the OFFSET inside SUMIF

Hello!

Please, take a look at the attached file.
I am referring to the SUMIF formula used throughout the column K.

The formula in K10 cell:

Formula:

Please Login or Register  to view this content.

I will try now to show how I understand it, and I will be grateful if you correct me and explain my mistakes.

(1)

Formula:

Please Login or Register  to view this content.

is the range to look at.

Formula:

Please Login or Register  to view this content.

means start at A10 and don't move anywhere (0,0), take only this A4 value (one row, one column).

But I don't understand the combination

Formula:

Please Login or Register  to view this content.

used here.
In any other circumstance I would read this as:
start at A3 and move all the way down to A10 , taking into account all values in each cell A4, A5, A6, etc.

But when I click on the formula I see that only A3 and A10 are highlighted. I assume that this means that the formula looks in each cell from the cell A3 to the cell A10 looking for a value specified in the second argument of the SUMIF function, is it correct?

(2)

Formula:

Please Login or Register  to view this content.

I am a bit confused by this syntax, but I assume it means that the criteria is the value in A10. How to correctly read this syntax?

If these above assumptions are correct, then why don't they simply use A10 as a value, and subsequently J10, without using OFFSETS and ranges A$3:OFFSET(A10,0,0,1,1) and J$3:OFFSET(J10,0,0,1,1). What value does this syntax add compared to simply referring to one cell?

Thank you very much!

The formula in K4 could be replaced by ..

=SUMIFS($J$4:J4,$A$4:A4,A4)-SUMIFS($I$4:I4,$A$4:A4,A4)

Put above in M4 and drag down

Does this make it easier to understand?

Basically we are checking a/c type in column A and subtracting column I from column J: the SUMIFS increment each time we find a match.

Originally Posted by JohnTopley

The formula in K4 could be replaced by ..

=SUMIFS($J$4:J4,$A$4:A4,A4)-SUMIFS($I$4:I4,$A$4:A4,A4)

Put above in M4 and drag down

Does this make it easier to understand?

Basically we are checking a/c type in column A and subtracting column I from column J: the SUMIFS increment each time we find a match.

Thank you very much for your reply. But unfortunately this did help me to understand my questions. I understand the main idea about what we do with this formula, i.e. subtracting I from J, but I am curious about syntax nuances, and how that works.

looks like disimproving.
This is more useful
Accounts!F19=SUM(F6:OFFSET(F19,-1,)), and no need ◄ Insert new rows above this one to add accounts

All of the OFFSETS simple reference the first range:

OFFSET(A5,0,0,1,1) references A5


This one simply references the previous row

OFFSET(L4,-1,0,1,1)+J4-I4) i.e L3

No idea why these are used as they serve no useful purpose

Originally Posted by JohnTopley

All of the OFFSETS simple reference the first range:

OFFSET(A5,0,0,1,1) references A5


This one simply references the previous row

OFFSET(L4,-1,0,1,1)+J4-I4) i.e L3

No idea why these are used as they serve no useful purpose

I see. Thank you very much. You sort of confirmed my assumption, because I also didn't see the purpose of these OFFSETS, but being a newbie I decided to ask here.
One more question, if I may. Please, explain a bit more why we use this syntax $J$4:J4 (or $A$4:A4) in

=SUMIFS($J$4:J4,$A$4:A4,A4)-SUMIFS($I$4:I4,$A$4:A4,A4)

Thank you!

"="&A10

the "&" acts as a concatenate (join) function:

so if cell A10="Credit"

then the above translates to "=Credit"

This allows the use of variables (A10)to hold the "match" value rather than "hard-coding" the value

If we put "=A10" this means the value is literally "A10" as opposed to the cell A10.

Another example: both the formulae below do the same i.e. check if column B contains "X" then SUMS the value in column C

=SUMIF(B:B,"=X",C:C)

=SUMIF(B:B,"=" &"X",C:C)

Hope this helps

Originally Posted by JohnTopley

"="&A10

the "&" acts as a concatenate (join) function:

so if cell A10="Credit"

then the above translates to "=Credit"

This allows the use of variables (A10)to hold the "match" value rather than "hard-coding" the value

If we put "=A10" this means the value is literally "A10" as opposed to the cell A10.

Another example: both the formulae below do the same i.e. check if column B contains "X" then SUMS the value in column C

=SUMIF(B:B,"=X",C:C)

=SUMIF(B:B,"=" &"X",C:C)

Hope this helps

Thank you very much! It is absolutely clear now. :-)