Sumifs/Index/Match

krazyness

New Member
Joined
Jan 31, 2017
Messages
23
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

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
Joined
Feb 6, 2012
Messages
1,796
It sounds like you should try and learn Pivot Tables.


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

krazyness

New Member
Joined
Jan 31, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 31, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
Feb 24, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,796
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.
 

krazyness

New Member
Joined
Jan 31, 2017
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top