Formula help

[hogiejax]

Hi everyone!

I am setting up a monthly personal budget in Excel, and I wanted to set
the spreadsheet to automatically pull totals from my checkbook (in
another Excel file) based on categories. In other words, my budget
would have a section for "gasoline" and the formula would search my
checkbook for "gasoline" items and then pull the totals from that
category over to the budget automatically. Does this make sense at
all? If so, let me know if there is a possible way to do
this.

 

[Guest]

If your descriptions are in Sheet1 A1:A6, and amounts in Sheet1 B1:B6, try:

=SUMPRODUCT(--(ISNUMBER(SEARCH("gasoline",[Checkbook.xls]Sheet1!A1:A6,1))),[Checkbook.xls]Sheet1!B1:B6)

Change source workbook, worksheet, and range references as needed.
Sumproduct cannot accomodate an entire column (A:A, A1:A65536)