SOLVED
Home

SumIfs Help

%3CLINGO-SUB%20id%3D%22lingo-sub-820048%22%20slang%3D%22en-US%22%3ESumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820048%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20getting%20errors%20using%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS('IS%20Detail%20By%20Company%20Base%20Accou'!%24E%2410%3A%24AK950%2C'IS%20Detail%20By%20Company%20Base%20Accou'!%24A%2410%3A%24A%24950%2C%24C8%2C'IS%20Detail%20By%20Company%20Base%20Accou'!%24E%243%3A%24AK%243%2CE%246)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20picture%20sample%20of%20the%20source%20data%20tab%20(not%20real%20data)%20and%20a%20picture%20of%20the%20errors%20tab%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128197i6347E87539F8BCB7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22data.jpg%22%20title%3D%22data.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128194iDC0945CF0FA117E1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22sumifs%20errors.jpg%22%20title%3D%22sumifs%20errors.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-820048%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESUMIFS%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820732%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396835%22%20target%3D%22_blank%22%3E%40bb94twins%3C%2FA%3E%26nbsp%3BCan%20you%20upload%20the%20sample%20file%3F%20It%20would%20be%20helpful%20to%20see%20the%20tabs%20and%20arrays%20in%20your%20spreadsheet.%20Happy%20to%20take%20a%20look.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820821%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396835%22%20target%3D%22_blank%22%3E%40bb94twins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESum%20range%20in%20SUMIFS%20shall%20be%201D%20range%2C%20not%202D.%20Try%20to%20use%20SUMPRODUCT%20instead%2C%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT('IS%20Detail%20By%20Company%20Base%20Accou'!%24E%2410%3A%24AK950*('IS%20Detail%20By%20Company%20Base%20Accou'!%24A%2410%3A%24A%24950%3D%24C8)*('IS%20Detail%20By%20Company%20Base%20Accou'!%24E%243%3A%24AK%243%3DE%246))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822682%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822682%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396133%22%20target%3D%22_blank%22%3E%40excelgeek%3C%2FA%3E%26nbsp%3B%20I%20have%20attached%20a%20file%20with%20some%20example%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822683%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822683%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%3BI%20had%20tried%20your%20suggestion%2C%20but%20wasn't%20able%20to%20get%20it%20to%20work%20either.%26nbsp%3B%20%26nbsp%3Bare%20you%20able%20to%20make%20adjustments%20to%20the%20file%20and%20repost%3F%26nbsp%3B%20I%20just%20need%20to%20sum%20by%20country%20listed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822701%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396835%22%20target%3D%22_blank%22%3E%40bb94twins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20adjust%20formula%20to%20your%20ranges%20it'll%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT('IS%20Detail%20By%20Company%20Base%20A%20(2)'!%24C%2410%3A%24AK%24950*('IS%20Detail%20By%20Company%20Base%20A%20(2)'!%24A%2410%3A%24A%24950%3D%24C8)*('IS%20Detail%20By%20Company%20Base%20A%20(2)'!%24C%243%3A%24AK%243%3DE%246))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822952%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822952%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%3BYes%2C%20that%20works!%26nbsp%3B%20%26nbsp%3BThank%20you!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822987%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822987%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396835%22%20target%3D%22_blank%22%3E%40bb94twins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhilst%20it%20is%20possible%20to%20use%20ranges%20other%20than%20a%20simple%20column%20array%2C%20it%20is%20rare%20that%20such%20a%20strategy%20is%20useful.%26nbsp%3B%20The%20problem%20with%20the%20x_IFS()%20functions%20are%20that%20the%20criterion%20parameters%20must%20be%20range%20references%20of%20the%20same%20shape%20as%20the%20data%20you%20are%20processing.%26nbsp%3B%20Thus%20the%20country%20row%20and%20the%20revenue%20category%20columns%20would%20each%20need%20to%20be%20broadcast%20to%20fill%20a%202D%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20that%20would%20be%20possible%20in%20your%20example%20would%20be%20to%20down-select%20the%202D%20data%20array%20to%20a%20single%20row%20by%20using%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20CHOOSE(%20index%2C%20row1%2C%20row2%20%E2%80%A6%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eor%20INDEX%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20INDEX(%20dataTable%2C%20index%2C%200%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ebefore%20using%20%3CSTRONG%3ESUMIFS%3C%2FSTRONG%3Ewith%20the%20country%20criterion%20only.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823080%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823080%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396835%22%20target%3D%22_blank%22%3E%40bb94twins%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823083%22%20slang%3D%22en-US%22%3ERe%3A%20SumIfs%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823083%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E.%26nbsp%3B%20Will%20need%20to%20play%20around%20with%20the%20Choose%20and%20index%20functions%20as%20I%20am%20not%20too%20familiar%20with%20them.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20the%20feedback%20and%20insight%3C%2FP%3E%3C%2FLINGO-BODY%3E
bb94twins
Occasional Contributor

I am getting errors using the sumifs function.

 

See attached file.

 

Appreciate any help.

 

Thank you.

9 Replies

@bb94twins Can you upload the sample file? It would be helpful to see the tabs and arrays in your spreadsheet. Happy to take a look. 

@bb94twins 

Sum range in SUMIFS shall be 1D range, not 2D. Try to use SUMPRODUCT instead, like

=SUMPRODUCT('IS Detail By Company Base Accou'!$E$10:$AK950*('IS Detail By Company Base Accou'!$A$10:$A$950=$C8)*('IS Detail By Company Base Accou'!$E$3:$AK$3=E$6))

 

@excelgeek  I have attached a file with some example data.

@Sergei Baklan I had tried your suggestion, but wasn't able to get it to work either.   are you able to make adjustments to the file and repost?  I just need to sum by country listed. 

 

Thanks.

Solution

@bb94twins 

If adjust formula to your ranges it'll be

=SUMPRODUCT('IS Detail By Company Base A (2)'!$C$10:$AK$950*('IS Detail By Company Base A (2)'!$A$10:$A$950=$C8)*('IS Detail By Company Base A (2)'!$C$3:$AK$3=E$6))

Please check attached

@Sergei Baklan Yes, that works!   Thank you!!

Highlighted

@bb94twins 

Whilst it is possible to use ranges other than a simple column array, it is rare that such a strategy is useful.  The problem with the x_IFS() functions are that the criterion parameters must be range references of the same shape as the data you are processing.  Thus the country row and the revenue category columns would each need to be broadcast to fill a 2D table.

 

Something that would be possible in your example would be to down-select the 2D data array to a single row by using 

= CHOOSE( index, row1, row2 … )

or INDEX

= INDEX( dataTable, index, 0 )

before using SUMIFS with the country criterion only.

@bb94twins , you are welcome

Thanks @Peter Bartholomew .  Will need to play around with the Choose and index functions as I am not too familiar with them.   

 

Appreciate the feedback and insight

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies