SUMIF question

%3CLINGO-SUB%20id%3D%22lingo-sub-1798911%22%20slang%3D%22en-US%22%3ESUMIF%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1798911%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%3C%2FP%3E%3CP%3E%3DSUMIF(B%3AB%3BI1%3BD%3AD)%3C%2FP%3E%3CP%3E%3DSUMIF(B%3AB%3BI2%3BD%3AD)%3C%2FP%3E%3CP%3E%3DSUMIF(B%3AB%3BI3%3BD%3AD)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eetc%2C%20but%20would%20like%20to%20use%26nbsp%3B%3DSUMIF(B%3AB%3BI%3AI%3BD%3AD)%20but%20it%20doesn't%20work.%20Is%20there%20a%20way%20to%20solve%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENils%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1798911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1799005%22%20slang%3D%22de-DE%22%3ESubject%3A%20SUMIF%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1799005%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F836817%22%20target%3D%22_blank%22%3E%40nilsr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%20Even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3EYou%20could%20get%20a%20precise%20solution%20much%20faster%20with%20a%20file%20(w%2Fout%20sensitive%20data).%3C%2FP%3E%3CP%3EThis%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3EIt%20is%20also%20helpful%20to%20know%20the%20operating%20system%20and%20Excel%20version%2C%20as%20different%20approaches%20may%20be%20required%20depending%20on%20the%20version%20and%20OS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EAt%20the%20same%20time%2C%20I%20would%20like%20to%20draw%20your%20attention%20to%20the%20fact%20that%20if%20you%20are%20on%20English%20excel%20and%20use%20English%20formulas%20you%20would%20have%20to%20have%20(%2C)%20instead%20of%20(%3B)%20in%20your%20formula.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3E%3CSTRONG%3EHere%20is%20a%20small%20example%3A%20%3D%3C%2FSTRONG%3E%20SUMIFS(%24B-7%3A%24B-37.%24I-7%3A%24I-%24D-7%3A%24D-37)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI%20don't%20know%20what%20this%20is%20supposed%20to%20help%2C%20but%20send%20it%20to%20you%20anyway%20if%20this%20is%20just%20the%20mistake%20for%20you.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi!

 

I use

=SUMIF(B:B;I1;D:D)

=SUMIF(B:B;I2;D:D)

=SUMIF(B:B;I3;D:D)

 

etc, but would like to use =SUMIF(B:B;I:I;D:D) but it doesn't work. Is there a way to solve it?

 

Thank you in advance!

 

Nils

2 Replies

@nilsr 

 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

You could get a precise solution much faster with a file (w/out sensitive data).

This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

It is also helpful to know the operating system and Excel version, as different approaches may be required depending on the version and OS.

 

At the same time, I would like to draw your attention to the fact that if you are on English excel and use English formulas you would have to have (,) instead of (;) in your formula.
Here is a small example: =SUMIFS($B$7:$B$37,$I$7:$I$37,$D$7:$D$37)

I don't know what this is supposed to help, but send it to you anyway if this is just the mistake for you.
 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@nilsr 

I wouldn't use entire columns, it slows down recalculation.

Let's say the criteria range is B2:B1000 and the sum range is D2:D1000.

The values to match are in I2:I15.

Select J2:J15.

Enter the following formula and confirm it with Ctrl+Shift+Enter to make it an array formula:

 

 

=SUMIF(B2:B1000,I2:I15,D2:D1000)

 

 

(Use ; instead of , if you use comma as decimal separator)