Forum Discussion

Copper Contributor

Oct 08, 2020

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

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: ...

BI & Data Analysis

excel

Formulas and Functions

office 365

training

HansVogelaar

MVP

Oct 08, 2020

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.

LaradonDex

Copper Contributor

Feb 09, 2022

Can you make a formula me if possible. I have to do the sum of instances of different columns in x with keywords such as elopement, functional communication etc occured. Column Y has amount of each word that corresponds to each keywords . I just need the total from each key work column. Thank you

HansVogelaar
MVP
Feb 09, 2022
LaradonDex

Could you attach a sample workbook, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
SAH_Excelhelper
Copper Contributor
Feb 09, 2022
LaradonDex I would recommend Pivot table over a formula to get these results and so much more, even using the slicer function once you have created the pivot table to further manipulate the data. If you want a formula to just calculate totals for each individual column with text datapoints you can use the =COUNTIF formula or =Sum.