Hi Team,
I would like to add and IF statement indicated by dropdown. For Example:
Dropdown Value would be: A, B, and C
Each Dropdown value will be tag to a pricing column.
I want to create some formula so where the value selected it will sum up the pricing values.
Sample of my Formula:
IF(AND({Deliverables#Labels} = “IG” ,{Deliverables#Labels}= “FB”), SUM({IG Rates},{FB Rates}),0)
The formula unable to work if I selected IG and FB in the dropdown.
Thanks,
Michael
Replace the section {Deliverables#Labels} = “IG” with: SEARCH(“IG”,Deliverables#Labels}&“IG”) <= LEN({Deliverables#Labels})
So your complete formula would be:
IF(AND(SEARCH("IG",{Deliverables#Labels}&"IG") <= LEN({Deliverables#Labels}), SEARCH("FB",{Deliverables#Labels}&"FB") <= LEN({Deliverables#Labels})),SUM({IG Rates},{FB Rates}),0)
Jim
Hi Jim,
Its working, Are you able to share some logic behind the formula? I am still need it to select more than 2 items and a single item.
Thanks,
Michael
Amazing - thanks @JCorrell. it helps. curious on the logic too
I will leave the IF function to another time. Here’s the logic for checking for specific dropdown values:
When you reference a dropdown column to return the labels, e.g.: {Deliverables#Labels}, it returns all of the labels selected delimited by comma & space in the order that they were selected. So, if “A”, “B” and “C” are all valid labels and for a given column value “C” and “A” were selected, {Deliverables#Labels} would return “C, A”. We can then use the SEARCH() function to see if it contains a particular value, like this: SEACRH(“A”, {Deliverables#Labels}). If found, the SEARCH() function returns the starting position where the value was found. In some systems a similar function will return zero if the value is not found. However, monday is returning an error in this case. So, to prevent the error we can make sure that it is always found by appending the value that we are searching for, e.g.: SEACRH(“A”, {Deliverables#Labels}&“A”). But, in this case we need to check if the find location was in the column value or the portion that we appended. If it was found in the column value the find location will be less than or equal to the length of the entire value. So, SEACRH(“A”, {Deliverables#Labels}&“A”) <= LEN( “A”, {Deliverables#Labels}) will return TRUE if “A” was in the column value and FALSE if it was in the appended section.
Now, if we want to check for more that 1 value concurrently, we can just string together similar searches inside an AND() function. AND(SEACRH(“A”, {Deliverables#Labels}&“A”), SEACRH(“B”, {Deliverables#Labels}&“B”), SEACRH(“C”, {Deliverables#Labels}&“C”)) will return TRUE if and ONLY if “A”, “B” and “C” are all found in the column value. If we wanted to check if ANY of these is found we can use OR() instead of AND(). OR(SEACRH(“A”, {Deliverables#Labels}&“A”), SEACRH(“B”, {Deliverables#Labels}&“B”), SEACRH(“C”, {Deliverables#Labels}&“C”)) will return TRUE if ANY of the values are found.
!!! One important caveat that must be considered: !!!
This will not work if some valid values are contained in other valid values. Specifically, if “A” and “CAT” are both valid values, when searching for “A” and “CAT” was selected, “A” would be found. To deal with this situation, one solution is to make sure that this can never happen by adding “start/end of value” characters to each value. So instead of “A” and “CAT” we could use “.A.” and “.CAT.”
Jim
Very helpful. Really appreciate it Jim!
Thanks so much for the explanation, Jim. Its really useful.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.