Home

Formula SUMIF - mistake

%3CLINGO-SUB%20id%3D%22lingo-sub-508523%22%20slang%3D%22en-US%22%3EFormula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508523%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20the%20formula%20SUMIF%20and%20found%20a%20mistake%20on%20my%20formula.%3C%2FP%3E%3CP%3E%3DSUMIF(K3%3AK371%3B%22A%22%3BJ2%3AJ371)%20instead%20of%26nbsp%3B%3DSUMIF(K3%3AK371%3B%22A%22%3BJ3%3AJ371).%3C%2FP%3E%3CP%3EAfter%20corrected%20the%20formula%2C%20I%20had%20a%20different%20total%20amount.%3C%2FP%3E%3CP%3ECould%20you%20please%20explain%20to%20me%20where%20does%20the%20variance%20come%20from%20knowing%20that%20the%20cell%20J2%20there%20is%20only%20a%20text%20(%22total%22)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20and%20best%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-508523%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508550%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331342%22%20target%3D%22_blank%22%3E%40Aureanne%3C%2FA%3E%26nbsp%3B%2C%20second%20formula%20returns%20correct%20result.%20With%20first%20formula%20SUMIF%20shifts%20on%20one%20cell%20up%20with%20cells%20to%20sum.%20For%20example%2C%20if%20you%20have%20A%20in%20J5%20and%20J7%2C%20SUMIFS%20returns%20K4%2BK6%20in%20first%20case%20and%20K5%2BK7%20for%20the%20correct%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508568%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331342%22%20target%3D%22_blank%22%3E%40Aureanne%3C%2FA%3E%26nbsp%3B%2C%20other%20words%20it%20finds%20positions%20of%20the%20cells%20which%20meet%20criteria%20in%20criteria%20range%20and%20sum%20cells%20on%20the%20same%20positions%20starting%20from%20the%20beginning%20of%20the%20sum%20range.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508626%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20a%20lot!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508636%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331342%22%20target%3D%22_blank%22%3E%40Aureanne%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508662%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508662%22%20slang%3D%22en-US%22%3ESUMIF%20requires%20that%20both%20range%20and%20sum_range%20arguments%20must%20always%20be%20equal%20in%20size%20and%20shape%3B%20otherwise%2C%20Excel%20coerces%20the%20sorter%20argument%20to%20conform%20to%20the%20longer%20one.%20When%20that%20happens%2C%20the%20result%20may%20be%20different%20from%20what%20you%20have%20expected.%3CBR%20%2F%3EMy%20simple%20advice%20is%20that%20don%E2%80%99t%20coerce%20Excel%20to%20make%20your%20formula%20conform%20to%20its%20syntax.%20Rather%2C%20always%20ensure%20that%20you%20conform%20to%20Excel%E2%80%99s%20syntax.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508722%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508722%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20SUMIF%20doesn't%20require%20what%20ranges%20have%20to%20be%20the%20same%20size%2C%20they%20could%20be%20different.%20Another%20story%20is%20what%20as%20a%20rule%20better%20to%20use%20same%20size%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508874%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508874%22%20slang%3D%22en-US%22%3EExcel%20insists%2C%20and%20so%20requires%2C%20both%20range%20and%20sum_range%20arguments%20to%20be%20equal.%20In%20the%20SUMIF%20syntax%2C%20just%20like%20law%2C%20the%20word%20%E2%80%9Crequire%E2%80%9D%20should%20be%20construed%20according%20to%20its%20spirit%20that%20gives%20life%20to%2C%20rather%20than%20according%20to%20its%20literal%20sense%20that%20kills%2C%20its%20true%20meaning.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508952%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20798px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111383iDA07F4C94012F4D3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-gb%2Farticle%2Fsumif-function-169b8c99-c05c-4483-a712-1697a653039b%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-gb%2Farticle%2Fsumif-function-169b8c99-c05c-4483-a712-1697a653039b%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20an%20example%2C%20this%20one%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20257px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111384i68E3529412F6CD37%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508995%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20SUMIF%20-%20mistake%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508995%22%20slang%3D%22en-US%22%3EThat%E2%80%99s%20exactly%20my%20emphasis%20earlier.%20I%20construed%20the%20word%20%E2%80%9Crequire%E2%80%9D%20according%20to%20its%20spirit%20that%20gives%20life%20to%20its%20true%20meaning.%20Apparently%2C%20you%20construed%20it%20according%20to%20its%20literal%20sense.%20I%20won%E2%80%99t%20insist%20how%20you%20construe%20it.%20That%E2%80%99s%20your%20prerogative%20and%20I%20respect%20it%2C%20although%20I%20won%E2%80%99t%20necessarily%20conform%20thereto.%3C%2FLINGO-BODY%3E
Highlighted
Aureanne
New Contributor

Hi,

 

I used the formula SUMIF and found a mistake on my formula.

=SUMIF(K3:K371;"A";J2:J371) instead of =SUMIF(K3:K371;"A";J3:J371).

After corrected the formula, I had a different total amount.

Could you please explain to me where does the variance come from knowing that the cell J2 there is only a text ("total")?

 

Thank you and best regards,

9 Replies
Highlighted

@Aureanne , second formula returns correct result. With first formula SUMIF shifts on one cell up with cells to sum. For example, if you have A in J5 and J7, SUMIFS returns K4+K6 in first case and K5+K7 for the correct formula.

Highlighted

@Aureanne , other words it finds positions of the cells which meet criteria in criteria range and sum cells on the same positions starting from the beginning of the sum range. 

Highlighted

@Sergei Baklan Thanks a lot!

Highlighted

@Aureanne , you are welcome

Highlighted
SUMIF requires that both range and sum_range arguments must always be equal in size and shape; otherwise, Excel coerces the sorter argument to conform to the longer one. When that happens, the result may be different from what you have expected.
My simple advice is that don’t coerce Excel to make your formula conform to its syntax. Rather, always ensure that you conform to Excel’s syntax.
Highlighted

@Twifoo , SUMIF doesn't require what ranges have to be the same size, they could be different. Another story is what as a rule better to use same size ranges.

Highlighted
Excel insists, and so requires, both range and sum_range arguments to be equal. In the SUMIF syntax, just like law, the word “require” should be construed according to its spirit that gives life to, rather than according to its literal sense that kills, its true meaning.
Highlighted
Highlighted
That’s exactly my emphasis earlier. I construed the word “require” according to its spirit that gives life to its true meaning. Apparently, you construed it according to its literal sense. I won’t insist how you construe it. That’s your prerogative and I respect it, although I won’t necessarily conform thereto.
Related Conversations
SumIf multiple columns
Gemma Telfer in Excel on
3 Replies
Drop Down & Sumif?
Lplayer92 in Excel on
3 Replies
Leap year formula
Hattsoff in Excel on
4 Replies