0

I have the following sheet that lists expenses by date and allows you to sort each payment into an expense category (pulled from a sheet).

I have an expenses sheet where I'd like to tally up all the values to the left of an expense category, then add those numbers up and insert the value into the appropriate cell in my 'expenses' sheet.

I know the steps I need to take are:

  • Get expense category for the column the cell resides in in the 'expenses' sheet.
  • Search for that category string within F4:F14 of my payments sheet.
  • Find all corresponding payments in rows where the expense category matches.
  • Tally them up.
  • Display them in the 'expenses' sheet in B2.

So far I have this but it's not working in any way so far, I'm not sure how to do a recursive function to pull a dynamic number of values together to tally them.

=VLOOKUP($B1,payments!$F3:F14,4,FALSE)

I'm looking to get 861 as the value for B2 in the expenses sheet.

payments sheet payments sheet

expenses sheet expenses sheet

  • You cannot use Vlookup for column on the left of the key column, Lookup array should be E3:F14 in all case no column 4 with the screenshot, where is April Advertising not showing, You have to use Index/Match – yass Apr 24 '17 at 20:15
  • @yass I'm having trouble understanding your comment. – eveo Apr 24 '17 at 20:17
  • First where is the amount for April Advertising? – yass Apr 24 '17 at 20:18
  • Second in order to use Vlookup the Lookup array (instead of F3:F14) has to contain the column Key on the Left of your Data (F3:F14 should be before E3:E14 the array will be 2 columns not 4 – yass Apr 24 '17 at 20:20
  • @yass I'm trying to tally up the advertising amount. So where it says 'Advertising' in column F, I wanna get all the values in column D for 'Advertising'. It sums up to 861, or it should, rather. I posted a solution that works. – eveo Apr 24 '17 at 20:33
1

Actually solved it with a simple =SUMIF(payments!$F3:$F14,B1,payments!$D3:$D14) and then just dragged it throughout the matrix and it worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.