Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Sum certain field cells based off certain criteria of the row


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!


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



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.


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


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.


for the amount, it's double


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


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

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


the account is "int32" 


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.