Need to enhance an IF(COUNTA formula.

[Jaimee001]

Good morning all,

I originally asked this question about 6 months ago and Hui provided me with a perfect formula. The original post heading was "help with a y/n formula"

I now need to tweak that formula.

I have a report that lists an account number, a settlement currency, submission currency 1 and submission currency 2

Column E is the original formula (IF(COUNTA(B2,C2)=2,IF(B2<>C2,"Y","N"),"") used to calculate accounts that are cross currency.

which was based on if the settlement currency and submission currency 1 didn't match then Y else N or blank


The data has changed so I need to formula to read in column D as well (submission currency 2).

So if the settlement currency is not equal to either submission currency 1 or submission currency 2 then Y else N or blank.


As you can see in my example, not all accounts submit at all or only have 1 submission currency.


Thank you in advance and please let me know if you have any questions.


Here's my file:

Download at SpeedyShare

 

[sdsurzh]

Hi Jai,


Can you please try the below formula in "F2" and check whether you are looking for this.


=IF(COUNTA(B2,C2,D2)=3,IF(B2<>C2,IF(C2<>D2,IF(D2<>B2,"Y","N"),""),""),"")


Thanks,

Suresh Kumar S

 

[Jaimee001]

Not quite, I only get back an 'N' where there is data in each of the columns.

The report may have fields that are blank.

There should always be something in the settlement currency (b) but the account may not have any submission currencies if they didn't submit or just 1 currency...

I know this is confusing....welcome to my world Thank you for all of your help...

 

[NARAYANK991]

Hi ,


If we list the situations which can arise , and the results that are expected , I think it will be clear what the formula should be :

[pre]

Settlement Currency         Submission Currency 1      Submission Currency 2       Result Expected
blank                                 blank                                blank
A                                    blank                                blank
A                                       A                                   blank
A                                       B                                   blank
A                                    blank                                   A
A                                    blank                                   B
A                                       A                                      A
A                                       A                                      B
A                                       B                                      A
A                                       B                                      B
A                                       B                                      C

[/pre]
Can you fill in the Result Expected column ?


Narayan

 

[Faseeh]

Hi Jaimee001,


Please check this formula, enter in H2 and drag down.

=IF(SUMPRODUCT((C2:D2=B2)*1)>=1,"Y","N")

Note: In cell F10, there is N while both currencies are same, In F3 you have written Y while both currencies are different ( I should be Y for F10 and N for F3 as i have understood)please explain.


Regards,