Sumifs/Index/Match

krazyness

New Member
Joined
Jan 31, 2017
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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 NumberClient Reference NumberAssign DateAssigned AmountPrincipal Duefee Duefee Paidfee TotalStsName1ClientJudgment DateTie SeriesTrailer
14​
132
4/16/2014​
6319.28​
0​
0​
91.5​
91.5​
PIFsmith,steveold
4/16/2014​
12102​
14​
65​
165
4/16/2014​
5616.67​
2611.45​
25​
13​
38​
LGLsmith,steveold
12102​
65​
159​
1610
4/16/2014​
24760.74​
0​
0​
0​
0​
LGLsmith,steveold
4/16/2014​
12102​
159​
17​
3
4/16/2014​
13427.38​
0​
0​
225​
225​
PIFjetson,georgeold
4/7/2014​
12593​
17​
19​
12
4/17/2014​
14658.02​
0​
0​
49​
49​
PIFjetson,georgeold
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 NumberTransaction CodeAmountTransaction DateACLIENTHEADER
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.

Please and thank you.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
It sounds like you should try and learn Pivot Tables.


Have a go, and message back if you can't get those results.
 
Upvote 0
Solution
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.
 
Upvote 0
@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.
 
Upvote 0
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
 
Upvote 0
Glad you sorted it, sorry I couldn't reply.

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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top