SUMIF Equations with data in multiple 6 column wide 'columns'.

[Kraner]

I am working on a new Financial Tracker for 2017. The basic structure is set, just need a few formulas.

The formulas I need for the first image would be something like:
In AF11 =SUMIF($AP$3,AA11,$AO$3)+SUMIF($AP$4,AA11,$AO$4)
In AG11 =SUMIF($AP$3,AB11,$AO$3)+SUMIF($AP$4,AB11,$AO$4)

BUT, I need it to work for all the 'receipts' to AAA. Each 'receipt' 6 columns wide with the data I need in the 5th and 4th column (Range and Sum Range respectively). I imagine some sort of INDEX function would work, would like it to be drag-able both over and down if possible.

The formula I need for the second image would be something like:
In D15 =SUMIF(AM6:AM100,B15,AO6:AO100)

The same restrictions apply, except the data is now in columns 2 and 4.

Thanks in advance and have a wonderful New Year!

 

[Rijnsent]

Hi Kraner,

thanks for the question, the images help understanding what you're looking for. To answer your question in an easy way: you could try to have a look at SUMPRODUCT and SUMIFS formulas, they could probably solve quite some parts of what you're trying to do. Having said that, I would start by creating a more data-table like sheet as a source for calculations.
You're currently inputting your data next to each other in your way, but in order to do calculations you want them in a table. In your situation I would probably create e.g.:

Receipt ID - Date - Description - Cat. - Sub (1) - TypeItem - Cost - Q - Part (select) - Code (select) - Receipt ID SUM (CHECK)
123 - 12/1-2016 - bill 1 - ^Food - Sit Down - Burger - 10.69 - 1 - PT(1) - AC-D - =SUMIF(Receipt ID, 123, Cost) -> 11.49
123 - 12/1-2016 - bill 1 - ^Food - Sit Down - Burger - 0.80 - 1 - PT(1) - AC-D - =SUMIF(Receipt ID, 123, Cost) -> 11.49
124 - 12/1-2016 - bill 2 - ^Other - Health - Chiropractor - 45.00 - 1 - PT(1) - EC-D - =SUMIF(Receipt ID, 124, Cost) -> 45.00

Using that table as a source, the SUMIFS formulas for your second sheet are peanuts ... I would put the data itself in a data table (Excel menu: Insert->Table) as that would make the formulas a bit more understandable. The selections would be with dropdowns (validation on the cell, preferably with named ranges).

Hope that gets you started,

Koen