Forum Discussion

KaKruger's avatar
KaKruger
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's avatar
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's avatar
    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's avatar
      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's avatar
      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. 

  • KaKruger's avatar
    KaKruger
    Copper Contributor
    Oct 12, 2020

    HansVogelaarcould 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

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 12, 2020

      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))

      • KaKruger's avatar
        KaKruger
        Copper Contributor
        Oct 13, 2020

        HansVogelaarTHANK YOU!!! It worked perfectly! Thanks so much for your help!

        Karen

  • KaKruger's avatar
    KaKruger
    Copper Contributor
    Oct 08, 2020

    Thank you HansVogelaar !

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

    Thanks again

    Karen

Resources