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

#### Kraner

##### New Member
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!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Rijnsent

##### Well-known Member
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

Replies
0
Views
219
Replies
1
Views
109
Replies
30
Views
745
Replies
3
Views
666
Replies
7
Views
250

1,191,684
Messages
5,987,994
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back