Oct 08 2020 06:43 AM
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
Oct 08 2020 06:48 AM
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.
Oct 08 2020 07:54 AM
Thank you @Hans Vogelaar !
I did have to remove the absolutes but then it worked perfectly!
Thanks again
Karen
Oct 12 2020 12:44 PM
@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
Oct 12 2020 02:38 PM
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))
Oct 13 2020 07:54 AM
@Hans VogelaarTHANK YOU!!! It worked perfectly! Thanks so much for your help!
Karen
Feb 09 2022 09:38 AM
Feb 09 2022 09:58 AM
@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.
Feb 09 2022 10:28 AM
Could you attach a sample workbook, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?