Sum certain field cells based off certain criteria of the row

[jrgreen]

Sorry if this question is either impossible or extremely easy to accomplish, I am still fairly new to Alteryx and can't seem to figure this one out. 

So I have a data set that consists of the following columns: Year, Account, Entity, and Amount. In my data set I have one entity that the amount for a certain account needs to be combined with another account and that row needs to be removed.

In short, I need some sort of formula like "If the account number is 56, take the amount from entity 5 and add it to the amount for entity 1 then delete the row for account 5"

Here is a screenshot to make life simpler. Thanks!

[hydrogurl01]

This might be something a simple formula tool can solve. 

You can try adding a formula tool to create a New Entity Column and using an IF/AND statement to write something like:

New Column Name: New Entity

EDITED FORMULA:

IF [Account]="56" && [Entity]="5" THEN "1" ELSE [ENTITY] ENDIF

This formula will essentially rename all your fields with an Account number of 56 and Entities with a '5' into being an Entity '1'. You can then use a Summarize tool to view a Summarized Sum view of the Amounts for each entity.

[jrgreen]

I am getting a "Malformed If Statement" error on the bolded section of the equation: IIF([Account]="56" && [Entity]="5","1",[ENTITY])

[hydrogurl01]

Apologies, just edited the formula I wrote in the reply. Also, is your Entity Field Type a Double or a V_String? If it is a Double, no need to put the quotes around the "5", you can leave it as just 5. You can also leave the "1" as just a 1 without the quotes if [Entity] is a double for you.

[jrgreen]

for the amount, it's double

[hydrogurl01]

Perfect. Is the [Entity] a string then? And does the edited formula I posted above work?

[jrgreen]

Yes, the entity is a string. And I'm still getting an error. It says "Invalid type in operator ==."

[hydrogurl01]

Hmm..is your [Account] field a String or is that a Double?

[jrgreen]

the account is "int32" 

[hydrogurl01]

That's probably the issue here. In our formula we are asking it to look for an Account with a String equivalent since we have quotes around it. Try changing the [Account] type to a String prior to this formula and see if that works.