Forum Discussion
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: 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
8 Replies
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.
- 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
Could you attach a sample workbook, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
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
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))
Thank you HansVogelaar !
I did have to remove the absolutes but then it worked perfectly!
Thanks again
Karen
