COUNTIF won't work with non-English/as simply "containing or not"?


#1

Hi support and all

In a numeric field I applied below formula

IFS(COUNTIF(C15,‘JJ shipping’,C15),5000,COUNTIF(C15,‘AAトランスポート’,C15),10000,0)

C15 is a selection field to enter transporter’s name.
If it’s JJ shipping they charge 5000 and AA Transport 10000 and that’s how I want to dictate the cost in the numeric field.

I just don’t get why this formula won’t work.
Is it because the counting text contains Japanese?(“トランスポート”=transport)
or that the COUNTIF function works differetly thatn Excel, cannot use as just a criteria of (if A field contains “B”), and actually counting sum of macthed?

I also tried LOOKUP formula but when I try
LOOKUP(C15,[‘JJ shipping’,‘AA トランスポート’],[5000,10000])
All the results will be 10000.


#2

Hi,

The IFS() isn’t a complete formula.

Correct syntax:
IFS(value=condition1,value_if_true1,value=condition2,value_if_true2,…,true,default value

Example:
IFS(A1=1,“Bad”,A1=2,“Good”,A1=3,“Excellent”,true,“No Valid Score”)

In your IFS(), there is no referenced field such as A1 in the above example.

Besides, the LOOKUP() function searches for a value in this list, which would need to be in ascending order. Hence, it’s more suitable for numeric fields instead of free text.

If you only have two types of transporter’s name, please try formulas below:
IF(C15.RAW=“JJ shipping”,5000,10000) or
IF(C15.RAW=“JJ shipping”,5000,IF(C15.RAW=“AAトランスポート”,10000,0))

Note: remember to manually trigger the formulas recalculation once the formulas updated in order to apply it to existing entries.

If you have more than two transporter’s names, you may use nested conditional formulas. Or, if you’re not familiar with that, you can feel free to explain the full conditional formulas you would like to have here or through a support ticket if you have security concerns so that we could help.


#3

Hi Angie

As you suggested I tried
IF(C15.RAW=“JJ shipping”,5000,IF(C15.RAW=“AAトランスポート”,10000,0))
but the formula is not working either.

Cannot figure out why.
C15 is a selection field in English and Japanese mixed.
Say C16=the field with this formula/for the result is numeric field.

Even “JJ Shipping”(all english) doesn’t show correct result so I guess it is not a language problem.


#4

What I actually want to do is, if in Excel
=IF(COUNTIF(C14,“JJ Shipping”),5500,IF(COUNTIF(C14,“AA Transport”),13500,0))

Because C14 is stock yard(=transporters’ name) with branches like “JJ Shipping Tokyo”,“JJ Shiping Osaka”,“AA Transport Nagoya” or “AA Transport Sapporo” and whether “JJ shipping” or “AA Transport” will decide the cost.

While C15 is handler’s name which can sometimes vary more than 2 source like “JJ Shipping”,“AA Transport” or a very few of “FF Liner” but without branch names.

So it will be very convenient for us if there is some way for “contain or not” function to work.


#5

Hi,

Can you allow the Ragic support staff access to your account so we can have a look at your current design?

You can allow access by clicking the “Need Help” link on the upper-right side of your account and filling the form.


#6

Hi Angie
I made a multiple sheet without sharing source as a testing environment.
Then tried the formula again and it worked.
But we still prefer to refer to C14(with branch names) instead of C15(without branch names but can vary) with “contain or not” funcion.

So for now we update C16(cost field) manually, in Excel file and uploading it.
I am hoping you could help me with the “contain or not” function.
If there is no such function prepared I will consider making IF formlua very long= refering C14 with conditions with all possible names with branches.


#7

Hi,

Can you allow the Ragic support staff access to your account so we can have a look at your current design?

You can allow access by clicking the “Need Help” link on the upper-right side of your account and filling the form.


#8

Hi Angie
I just did.
Please note that cells/names/numbers above aren’t real ones.
I think your stuff should contact me first.


#9

Hi Angie
Thanks for taking a look and all your advise on e-mail.
For now we decided to go with
IF(OR(…)=TRUE,5000,IF(OR(////)=TRUE,10000,0))
And putting all transporters names with branches within OR().


#10

Hi,

That would work as well. The only shortcoming would be you’ll need to modify the formulas whenever you have new branches. :slight_smile:


#11

Yeah that is also a case.
For now we don’t want extra work/modification everytime importing from excel but do as it is, having Ragic’s help being easy to change designs.