Hi

I'm trying to do a classic sumif with multiple statements, but I can't seem to make it work. In my concrete case, I would also like to sum in another table. So, my attempt was something along the lines of:

=SumIf({Cost Source Master Data.Cost Pool}="Internal Labor" AND {Cost Source Master Data.Is Labor}="Yes",{Cost Source Master Data.Amount})

So the issue is twofold: Making multiple 'if' for sum and referencing other dataset.

BR,

Jesper

I think I have a solution but I do not know if this the best way, others should judge:

1) Define new variable: LH_Internal Labor_Is Labor=Cost Source Master Data.Cost Pool&&Cost Source Master Data.Is Labor in the table Cost Source Master Data (LH stand for Lookup helper).

You have to do something similar in the table where you sum up, so you can do can do a LookupEx on the table and get all data in the from Cost Source Master. But the data will NOT be summed up.

To solve these there are several possibilities:

1) Make a copy Cost Source Master Data and add a group items by "Cost Pool" and "Is Labor" and use this table in the table where you need the value

2) Add the group by step in the table where you need the value

3) Do the sumif here but then you will still have rows you looked up in Cost Source Master Data

I hope that helps!

I agree with @Susanne Welker's suggestion here - the substitute for "SumIfs" in TBM Studio is the Lookup function.

In situations where I have to gather summed values into Table A from Table B, I first create a transform of Table B for  "SumIf" purposes. I then group-down Table B Transform to the level of granularity of my look-up key (e.g. Cost Source Master Data.Cost Pool AND Cost Source Master Data.Is Labor). This will cause all the numeric fields to sum-up and can then be pulled into Table A via Lookup.

Do the sumif in (the table appended to) Cost Source MD based on a new concatenated field, then append sumif to Cost Source MD.

Thanks for the answers.

What I gather is that you cannot get the sum of a column in a different table. So I basically need to create new column and append or do a lookup?

Yes, that is easiest.

Hi @Jesper L�rkedal,

Goto the Help and look for Data Lookup. I think it will work for your usecase.

It allows you to get a sum of a numerical column in another table based on filtering criteria. 

So - in your case from another table - this formula should work:

=Cost Source Master Data:Amount[Cost Pool="Internal Labor" AND Is Labor="Yes"]

If you want it to be dynamic based on the row values of another table, that also works:

Lets say you have a table with following columns:

Labor Cost Pool,Cost Center,Amt

Internal Labor,8000,500

Internal Labor,8001,600

Internal Labor,8002,700

External Labor,8001,300

External Labor,8002,200

External Labor,8003,100

And you want to calculate the percent of total Internal / External Labor Cost, you can have a formula to lookup only the total Internal Labor or External Labor by using the formula:

=Cost Source Master Data:Amount[Cost Pool=Labor Cost Pool AND Is Labor="Yes"]

this way the Cost Pool is matched with the value of the Labor Cost Pool  column in the current row.

Let me know if you have questions.

Regards,

Sanjay Valiyaveettil

Including the help details here:

The syntax for external data lookup is:

={table}:{column1}[column2 selector]

This means return the value in column1 of the named table, where column2 matches the selector.

Note: Unlike the syntax for functions, the brackets in this row-select syntax must be included if the optional column selector is used.

The selector can be any of the following:

operator match (where match can be a string or a column reference and the operator can be = (equal to) or != (not equal to))

IN ( string [ ,...n ] )

NOT IN ( string [ ,...n ] ) (Note: there is a space between NOT and IN)

 If more than one value matches the selector, the reference returns the sum of the matching values.

If the optional column2selector is not specified, the reference returns the sum of column1.

hi5 @Sanjay Valiyaveettil. You beat me to the punch!

I, too, strongly encourage use of the Data Lookup approach for @Jesper L�rkedal's use case.

Check out the TBMC document for it here:

https://tbmcouncil.jiveon.com/docs/DOC-4990

Thanks @Matt Temple. Didn't realize how important it is to use the @mentions. Will follow your lead.

Hey @Jesper L�rkedal - we need to get more tips from Matt.

Thanks a lot, @Sanjay Valiyaveettil I think I stuck a bit too long with the lookup-formula, which obviously didn't work