 # 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

5 Replies

# Re: Advanced Excel formula to pull \$ based on keywords from 3 different worksheets

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.

# Re: Advanced Excel formula to pull \$ based on keywords from 3 different worksheets

Thank you @Hans Vogelaar !

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

Thanks again

Karen

# Re: Advanced Excel formula to pull \$ based on keywords from 3 different worksheets

@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

# Re: Advanced Excel formula to pull \$ based on keywords from 3 different worksheets

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

# Re: Advanced Excel formula to pull \$ based on keywords from 3 different worksheets

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

Karen