Sum Depending On Match Between Value And Its Existence In A Table

[aaronb]

Please see post number 5

[Safal Shrestha]

Can you please attach a sample workbook? It can help me to understand clearly and try to solve your problem. I think I can solve it if I get the clear picture.

Br,

[aaronb]

I decided to display the values separate rather than summing them so that problem is resolved.

That said, I now have a much more challenging problem to tackle and any assistance is much, much, appreciated. I am almost positive this will require VBA, but if you can do it with a formula alone, I will be extremely surprised.

Basically, I am trying to get a way to copy all stock names from two sources in alphabetical order, without duplicates, to a final table. The stock names come from two sources: a positions table, and a history table.


Criteria:

1) It should only get stocks for accounts that exist in the Accounts Table (applies to the next two criteria).

2) It should only get stocks from the Positions table from the predefined quarter/year (in the workbook, $P$16 and $P$17)

3) It should only get stocks from the History table if the transaction quarter/year match the predefined quarter/year. If the stock has a settlement quarter/year other than 0, it should only get the stock if those numbers (rather than the transaction quarter/year numbers) match the predefined quarter/year.

4) When it's done, it should remove any rows in the final table in which the formulas for Pre Value and Post Value both return the value 0. For my actual need, those are not the formulas that will be in the Pre/Post Value cells, if you need those I can PM them to you but they are quite lengthy and intricate and there are actually more formula columns than that. In my case, if all the formula columns returned 0 the entire row should be removed.


I'm sure that doesn't make 100% sense so please see the attached workbook. I have an example Position table, History table, Accounts table, Formulas table (though you shouldn't use it, that's just for the formulas in columns of the resulting table), and what the final result table should look like before one of its rows is deleted. After looking at it, everything should be very clear.

749

[Safal Shrestha]

Sorry, I am bad with VBA. And as per my knowledge I don't think the records can be displayed without writing VB code.

Sorry again.

Br,

[aaronb]

Back to the original question. I have a simple example that I should be able to adapt the solution to my project but need help figuring the formula out.


I want to sum all numbers in Table[Column2] if the security in Table[Column1] is of type "Stock". The security-type correspondence table is Table2, with the securities in Column1 of Table 2 and the type in Column2. If the security was not found in the correspondence table, the formula should give some sort of error indication.

So far I have tried sumproduct and using sumif/s with and without index/match or vlookup but can't seem to get it.

For example:

Table1:


Security
Value


A
1


A
31


B
5


C
10


D
16


E
23



Table2:


Security
Type


A
Stock


B
Bond


C
Bond


D
Stock


E
Bond



= 1 + 31 + 16 = 48

Thank you for any help!

[Excel Fox]

Use this

=SUM((Table1[Security]=TRANSPOSE(IF(Table2[Type]="Stock",Table2[Security],"")))*(Table1[Value]))

[aaronb]

Works like a charm, thanks.. never would have thought to use Transpose.