# Sumifs/Index/Match

#### krazyness

##### New Member
Good Day All!
There are millions of articles and helpers and i can't get anything to match with what my data is telling me i need to have. Nothing ever gives me a correct picture, or i'm just too dumb to be doing numbers. there's so many circular references i don't know how to separate the data.

I beg for help.

I need to sum the amount in Assigned Amount (D) when all instances of Tie Series(M) are found. For example account number 14, 65,159, may be the same "tie series" 12102. So I should get a total of \$36,696.69.

sheet: accounts
 Account Number Client Reference Number Assign Date Assigned Amount Principal Due fee Due fee Paid fee Total Sts Name1 Client Judgment Date Tie Series Trailer 14​ 132 4/16/2014​ 6319.28​ 0​ 0​ 91.5​ 91.5​ PIF smith,steve old 4/16/2014​ 12102​ 14​
 65​ 165 4/16/2014​ 5616.67​ 2611.45​ 25​ 13​ 38​ LGL smith,steve old 12102​ 65​ 159​ 1610 4/16/2014​ 24760.74​ 0​ 0​ 0​ 0​ LGL smith,steve old 4/16/2014​ 12102​ 159​
 17​ 3 4/16/2014​ 13427.38​ 0​ 0​ 225​ 225​ PIF jetson,george old 4/7/2014​ 12593​ 17​ 19​ 12 4/17/2014​ 14658.02​ 0​ 0​ 49​ 49​ PIF jetson,george old 4/22/2014​ 12593​ 19​

Then I need to look in the transactions sheet and separate the Assigned Amount if there is a transaction code (B)= 300 with that matching account number. So i would have the 300 value to be 36,696.69, and the others would be excluded.

sheet: transactions

 Account Number Transaction Code Amount Transaction Date ACLIENT HEADER 14​ 300​ 76.96​ 12/16/2014​ old 12102​
 17​ 1​ 25​ 12/16/2014​ old 12593​

Hopefully this makes sense what i'm looking for and someone can provide help.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### mrshl9898

##### Well-known Member
It sounds like you should try and learn Pivot Tables.

Have a go, and message back if you can't get those results.

#### krazyness

##### New Member
I agree I probably need to learn pivot tables... i took to learning powerbi a couple years ago instead. this data though. it kicks my butt in all things everything. i have the pivot table for one sheet...i've yet to come across info to link two different sheets.

thanks for the suggestion.

#### krazyness

##### New Member
@mrshl9898 - pivot tables and powerbi are virtually the same thing... i like pivot tables better.

What I can't seem to figure out though is how to sort the info if that 300 transaction code exists, and split that way. you have my head in a whole new world of problems now, but i feel like i'm coser than i did this morning.

#### richelg

##### New Member

Hi, I'm trying to combined if formula and roundup for our tiering calculation but I couldn't find the correct formula to get the result I wanted, so if someone can help that will be greatly appreciated
so we have a monthly expenditure and we take the percentage of the monthly expenditure against to the overall total to the the % monthly expenditure and then that percentage and being mutiplied to the tier table and I want the result to be automatically round up rather than adding up another column and using the roundup formula

#### krazyness

##### New Member
i have solved my problem. thank you for the nudge to pivot tables @mrshl9898

#### mrshl9898

##### Well-known Member

FYI Excel also has Power Pivot, you just need to enable it. Same as BI you add multiple data sources and create relationships.

May be worth a look if you'd rather use Excel. We went to BI a while back, but for many things i've reverted back to Excel.

#### krazyness

##### New Member
i like excel so much better... means i don't have to know DAX, because i can't understand that stuff. i tried using the power pivot but it wasn't quite giving me the results i wanted.

I'm dealing with the last bit of data sorting... i think i went about it the long way.. i'm sure there are cleaner easier ways.

Replies
7
Views
125
Replies
11
Views
193
Replies
9
Views
95
Replies
5
Views
115
Replies
5
Views
247

1,130,421
Messages
5,642,018
Members
417,250
Latest member
spr1nger

### 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