SOLVED
Home

Sum of Rows with Matching Cell Identifier

%3CLINGO-SUB%20id%3D%22lingo-sub-882917%22%20slang%3D%22en-US%22%3ESum%20of%20Rows%20with%20Matching%20Cell%20Identifier%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882917%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20formula%2C%20NOT%20a%20Pivot%20Table%2C%20to%20complete%20the%20following%3A%3C%2FP%3E%3CP%3EFind%20the%20sum%20of%20all%20(VALUE)%20rows%20matching%20by%20all%20(EMPLOYEE%20ID)%20rows.%3C%2FP%3E%3CP%3ESo%20for%20the%20example%20below%2C%20my%20ideal%20result%20would%20show%20that%20Employee%20ID%20%23%201201%20has%20a%20total%20of%207%2C%20where%20Employee%20ID%20%231426%20has%20a%20total%20of%2010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EEmployee%20ID%20%23%3C%2FTD%3E%3CTD%3EValue%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1201%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1426%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1201%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-882917%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882932%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20Rows%20with%20Matching%20Cell%20Identifier%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882932%22%20slang%3D%22en-US%22%3EThis%20could%20be%20accomplished%20with%20a%20simple%20SUMIF()%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882976%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20Rows%20with%20Matching%20Cell%20Identifier%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417511%22%20target%3D%22_blank%22%3E%40Kaw1988%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20data%20is%20setup%20like%20below...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20661px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134162iA134D35199420B2C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SUMIF.jpg%22%20title%3D%22SUMIF.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20the%20SUMIF%20function%20to%20get%20the%20desired%20output....%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMIF(A%3AA%2CD2%2CB%3AB)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20above%20formula%20assumes%20that%20the%20Employee%20IDs%20are%20placed%20in%20column%20A%20and%20Values%20are%20places%20in%20column%20B%20and%20the%20D2%20contains%20the%20lookup%20Employee%20ID.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20data%20is%20setup%20differently%2C%20change%20the%20range%20reference%20in%20the%20formula%20as%20required.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882980%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20Rows%20with%20Matching%20Cell%20Identifier%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417511%22%20target%3D%22_blank%22%3E%40Kaw1988%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20afternoon%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20looking%20for%20a%20formula%2C%20NOT%20a%20Pivot%20Table%2C%20to%26nbsp%3Bsum%20of%20all%20(VALUE)%20rows%20matching%20by%20all%20(EMPLOYEE%20ID)%20rows%20as%20per%20your%20below%20example%20and%20the%20ideal%20result%20should%20be%207%20for%20Employee%20ID%20%23%201201.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20172px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134167i29E922CC44F7F726%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%2202.PNG%22%20title%3D%2202.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EYou%20can%20use%20%22SUMIF%22%20as%20shown%20in%20illustration%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20436px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134165iFFAB073DEEE3F330%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%2201.png%22%20title%3D%2201.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-883306%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20Rows%20with%20Matching%20Cell%20Identifier%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-883306%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much!%20So%20much%20easier%20than%20I%20anticipated.%20I%20appreciate%20your%20answer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-883332%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20of%20Rows%20with%20Matching%20Cell%20Identifier%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-883332%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome!%20Glad%20I%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Good afternoon,

 

I am looking for a formula, NOT a Pivot Table, to complete the following:

Find the sum of all (VALUE) rows matching by all (EMPLOYEE ID) rows.

So for the example below, my ideal result would show that Employee ID # 1201 has a total of 7, where Employee ID #1426 has a total of 10.

 

Employee ID #Value
12015
142610
12012
5 Replies
This could be accomplished with a simple SUMIF() formula.
Solution

@Deleted 

If your data is setup like below...

SUMIF.jpg

 

You may try the SUMIF function to get the desired output....

=SUMIF(A:A,D2,B:B)

The above formula assumes that the Employee IDs are placed in column A and Values are places in column B and the D2 contains the lookup Employee ID.

 

If your data is setup differently, change the range reference in the formula as required.

@Deleted 

Good afternoon,

 

You are looking for a formula, NOT a Pivot Table, to sum of all (VALUE) rows matching by all (EMPLOYEE ID) rows as per your below example and the ideal result should be 7 for Employee ID # 1201.

02.PNG

You can use "SUMIF" as shown in illustration below:

01.png

 
 

@Subodh_Tiwari_sktneer Thank you very much! So much easier than I anticipated. I appreciate your answer.

You're welcome! Glad I could help.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies