Advanced Excel formula to pull $ based on keywords from 3 different worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1758847%22%20slang%3D%22en-US%22%3EAdvanced%20Excel%20formula%20to%20pull%20%24%20based%20on%20keywords%20from%203%20different%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1758847%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I've%20done%20basic%20formulas%20in%20Excel%20but%20now%20I%20need%20one%20to%20pull%20info%20from%20multiple%20sheets%20from%20one%20column%20based%20on%20a%20keyword%20from%20another%20column%2C%20and%20sum%20total%20it%20in%20its%20destination%20cell.%20Example%3A%20if%20column%20F%20on%20worksheet%20A%2C%20B%20%26amp%3B%20C%20has%20the%20keyword%20%22utilities%22%2C%20then%20pull%20the%20dollar%20amount%20from%20column%20D%20and%20sum%20total%20it%20in%20cell%20B13%20on%20worksheet%20D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sure%20this%20is%20possible%2C%20I've%20just%20not%20found%20anything%20in%20my%20searches.%20I%20appreciate%20any%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EKaren%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1758847%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1759185%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Excel%20formula%20to%20pull%20%24%20based%20on%20keywords%20from%203%20different%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1759185%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20!%3C%2FP%3E%3CP%3EI%20did%20have%20to%20remove%20the%20absolutes%20but%20then%20it%20worked%20perfectly!%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3CP%3EKaren%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1772704%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20Excel%20formula%20to%20pull%20%24%20based%20on%20keywords%20from%203%20different%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1772704%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3Ecould%20you%20be%20so%20kind%20to%20help%20me%20with%20another%3F%20I'm%20trying%20now%20to%20break%20down%20the%20info%20from%20the%20three%20worksheets%20by%20month%20the%20expense%20occurred%20-%20so%20pulling%20the%20same%20info%20as%20last%20time%20except%20only%20if%20from%20column%20G%20the%20specific%20month%20is%20listed.%20Every%20time%20I%20try%20a%20formula%20it%20tells%20me%20I've%20added%20too%20many%20actions.%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help%20you%20can%20offer%3C%2FP%3E%3CP%3EKaren%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi - I've done basic formulas in Excel but now I need one to pull info from multiple sheets from one column based on a keyword from another column, and sum total it in its destination cell. Example: if column F on worksheet A, B & C has the keyword "utilities", then pull the dollar amount from column D and sum total it in cell B13 on worksheet D.

 

I'm sure this is possible, I've just not found anything in my searches. I appreciate any help!

 

Thanks!

Karen

5 Replies
Highlighted

@KaKruger 

Simply add the sums for each of the worksheets:

 

=SUMIF('Sheet A'!$F$2:$F$1000, "utilities", 'Sheet A'!$D$2:$D$1000)+SUMIF('Sheet B'!$F$2:$F$1000, "utilities", 'Sheet B'!$D$2:$D$1000)+SUMIF('Sheet C'!$F$2:$F$1000, "utilities", 'Sheet C'!$D$2:$D$1000)

 

Substitute the actual names of the sheets, and adjust the ranges if they extend below row 1000.

Highlighted

Thank you @Hans Vogelaar !

I did have to remove the absolutes but then it worked perfectly!

Thanks again

Karen

Highlighted

@Hans Vogelaarcould you be so kind to help me with another? I'm trying now to break down the info from the three worksheets by month the expense occurred - so pulling the same info as last time except only if from column G the specific month is listed. Every time I try a formula it tells me I've added too many actions.

Thank you for any help you can offer

Karen

Highlighted

@KaKruger 

Something like this:

 

SUMIFS('Sheet A'!$D$2:$D$1000, 'Sheet A'!$F$2:$F$1000, "utilities", 'Sheet A'!$G$2:$G$1000, ">="&DATE(2020, 10, 1), 'Sheet A'!$G$2:$G$1000, "<="&DATE(2020, 10, 31))+SUMIFS('Sheet B'!$D$2:$D$1000, 'Sheet B'!$F$2:$F$1000, "utilities", 'Sheet B'!$G$2:$G$1000, ">="&DATE(2020, 10, 1), 'Sheet B'!$G$2:$G$1000, "<="&DATE(2020, 10, 31))+SUMIFS('Sheet C'!$D$2:$D$1000, 'Sheet C'!$F$2:$F$1000, "utilities", 'Sheet C'!$G$2:$G$1000, ">="&DATE(2020, 10, 1), 'Sheet C'!$G$2:$G$1000, "<="&DATE(2020, 10, 31))

Highlighted

@Hans VogelaarTHANK YOU!!! It worked perfectly! Thanks so much for your help!

Karen