SUMIF - sum by any criteria possible?

%3CLINGO-SUB%20id%3D%22lingo-sub-1492298%22%20slang%3D%22en-US%22%3ESUMIF%20-%20sum%20by%20any%20criteria%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492298%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20want%20to%20have%20my%20set%20of%20data%20summed%20up%20by%20each%20country%20as%20in%20the%20second%20table%20below.%20Though%2C%20is%20there%20any%20way%20I%20can%20get%20a%20sum%20of%20each%20country%20(i%20e%20each%20criteria)%20without%20having%20to%20specifically%20define%20each%20country%3F%20In%20other%20words%2C%20can%20Excel%20automatically%20produce%20the%20complete%20second%20table%3F%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EHelli%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%20image-alt%3D%22helli76_0-1593183363367.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201313i49161992DBCAC578%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22helli76_0-1593183363367.png%22%20alt%3D%22helli76_0-1593183363367.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1492298%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-1492419%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20-%20sum%20by%20any%20criteria%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492419%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711368%22%20target%3D%22_blank%22%3E%40helli76%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20pivot%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1492421%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20-%20sum%20by%20any%20criteria%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492421%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711368%22%20target%3D%22_blank%22%3E%40helli76%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20a%20SUMIF%20then%20the%20answer%20(I'm%20afraid)%20is%20no.%3C%2FP%3E%3CP%3EBut%20if%20you%20use%20a%20pivot%20table%20-%20see%20example%20below%20-%20it%20will%20work%20easily.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20that%20helps!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1492431%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20-%20sum%20by%20any%20criteria%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711368%22%20target%3D%22_blank%22%3E%40helli76%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEasier%20way%20is%20by%20creating%20pivot%20table%20from%20the%20data.%3C%2FP%3E%3CP%3EPFA%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20conver%20the%20data%20into%20a%20table%2C%20(Select%20all%2C%20and%20click%20Control%2B%20%22T%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Insert%20--%26gt%3B%20pivot%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20drop%20the%20fields%20as%20below%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SupreethGuptha_0-1593186631666.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201331i5CEBD9AEF6F327FA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22SupreethGuptha_0-1593186631666.png%22%20alt%3D%22SupreethGuptha_0-1593186631666.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E4.%20and%20you%20will%20get%20the%20same%20data%2C%20which%20you%20can%20sort%20too.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SupreethGuptha_1-1593186680722.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201332i328A9CB326A13607%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22SupreethGuptha_1-1593186680722.png%22%20alt%3D%22SupreethGuptha_1-1593186680722.png%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%3EHope%20this%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EContact%20me%20for%20more%20detailed%20info%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3ESupreeth%3C%2FP%3E%3CP%3E%3CA%20href%3D%22mailto%3Asupreeth.guptha%40gmail.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Esupreeth.guptha%40gmail.com%3C%2FA%3E%3C%2FP%3E%3CP%3E8904609867%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495893%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20-%20sum%20by%20any%20criteria%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495893%22%20slang%3D%22en-US%22%3E%3CP%3EOf%20course%2C%20thanks!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495895%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20-%20sum%20by%20any%20criteria%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495895%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20also%20for%20the%20instruction!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495897%22%20slang%3D%22en-US%22%3ERe%3A%20SUMIF%20-%20sum%20by%20any%20criteria%20possible%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495897%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20a%20lot%2C%20will%20now%20make%20sure%20to%20learn%20to%20pivot!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F697164%22%20target%3D%22_blank%22%3E%40SupreethGuptha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi!

I want to have my set of data summed up by each country as in the second table below. Though, is there any way I can get a sum of each country (i e each criteria) without having to specifically define each country? In other words, can Excel automatically produce the complete second table?

Thanks!

Helli

 

helli76_0-1593183363367.png

 

6 Replies

@helli76 

 

You can use pivot table

 

Cheers

@helli76 

 

Using a SUMIF then the answer (I'm afraid) is no.

But if you use a pivot table - see example below - it will work easily.

 

hope that helps!

 

Peter

@helli76 

 

Hi

 

Easier way is by creating pivot table from the data.

PFA

 

1. conver the data into a table, (Select all, and click Control+ "T")

 

2. Insert --> pivot table

 

3. drop the fields as below

SupreethGuptha_0-1593186631666.png

4. and you will get the same data, which you can sort too.

SupreethGuptha_1-1593186680722.png

 

 

Hope this helps.

 

Contact me for more detailed info

 

Regards

Supreeth

supreeth.guptha@gmail.com

8904609867

 

Of course, thanks! @wumolad 

Thanks, also for the instruction! @peteryac60 

Thanks a lot, will now make sure to learn to pivot! @SupreethGuptha