SUMIF's with TEXT

[lynchbro]

I am trying to create a formula to look up off of a master sheet to return the sum of account balances by investor numbers for a particular group denoted in the master sheet.

I currently have 3 tabs, TICO, RCH & PCM. Each of these tabs have investor account numbers with their allocations.

Then the master sheet has all of the account numbers broken out by the 3 groups.

On the individual group tabs I would like to pull the ending balance data from the master sheet. Each group tab in Cell A1 has the name of the group with matches back to what is on the master sheet.

i thought i would have to do:

=IF(A1=MasterSheet!A:A,SUMIF(MasterSheet!B:B,TICO!B9,MasterSheet!C:C))

Where on the master sheet A:A represents the group name, B:B represents the investor account numbers & C:C represents the ending balances.

And the TICO B9 is the investor account number for that group.

Any help!?

 

[Jonmo1]

Welcome to the board..

Try

=SUMIFS(MasterSheet!B:B,TICO!B9,MasterSheet!C:C,MasterSheet!A:A,TICO!A1)

 

[lynchbro]

That is returning #VALUE!

What could be causing this?

 

[Jonmo1]

My Bad...

=SUMIFS(MasterSheet!C:C,MasterSheet!B:B,TICO!B9,MasterSheet!A:A,TICO!A1)

 

[lynchbro]

Hi Jono,

Thanks for your help prior, but now what I need to perform just got alot harder.

I still need all the criteria, but now each investment will have a lot number assigned to it & each investor could have multiple lots.

Is there a way to build on the below?

Master sheet References
A:A represents the group name
B:B represents the investor account numbers
C:C represents the ending balances
D:D represents the lot numbers

Thanks so much!!

 

[Jonmo1]

Try
=SUMIFS(MasterSheet!C:C,MasterSheet!B:B,TICO!B9,MasterSheet!A:A,TICO!A1,MasterSheet!D:D,TICO!C18)

C18 = The Lot # of interest.

 

[lynchbro]

is this still a sumifs? we no longer need to sum any quantities, just lookup and reference.

 

[Jonmo1]

Well, based on your last post (and that it was nearly a month after the suggested solution), it seemed that the previous suggestion did indeed work as you wanted.
But now you only wanted to add 1 more criteria for a Lot #.

So yes, it's still doing what it was doing before, just with 1 more criteria (lot #).

So do you actually want something entirely diffent then?
Are you now just wanting a COUNT of how many records meet the criteria?

If that's the case, then it would be

=COUNTIFS(MasterSheet!B:B,TICO!B9,MasterSheet!A:A,TICO!A1,MasterSheet!D:D,TICO!C18)

Again, still using the same criteria (+ the new criteria of Lot #)
But it's now COUNTING instead of SUMMING.

 

[lynchbro]

I'm sorry for the confusion. Before I did not realize that I need to reconcile by individual lots, so the SUM was the correct way to go. But now this is not the case. Here is a new example. I hope that you are able to help and I am clarifying all the issues I need.

I have a master spreadsheet with the following fields:

A:A represents the group name
B:B represents the investor account numbers
C:C represents the ending balances
D:D represents the lot numbers

Then I have individual tabs for each of the groups. Within each tab on the groups there are can be multiple investments broken out by LOT number.

On the individual group tabs I have the following fields:
A1 represents the group name
Column B represents the investor account number
Column C represents the lot number of the investment

What I am looking to do is pull the ending balances by investor account numbers and lots into each tab.

For example:

Investor A has investments in all 3 groups. Within group A he has 2 investments denoted by lot number.

Once I can pull the ending balance from the master sheet by investor account number & lot number, I then have another column to compare that balance to my manual calculations.

 

[Jonmo1]

Then I don't understand how post #6 wasn't helpful. Sounds like exactly what you need.

Sum from the master sheet column C, where:
MasterSheet Col A = A? from group tab (the groupname of interest)
MasterSheet Col B = B? from group tab (the account # of interest)
MasterSheet Col D = C? from group tab (the lot # of interest)