0

I use two formulas for my purpose:

In B2: If string start (begins) with substrings combination, then delete first substring

=IF(SUM(COUNTIF(A2;{"Not";"Bad"}&" "&{"WoW"\"Super"\"Excellent"\"Right"\"Perfect"\"Great"\"Ideal"}&"*"));RIGHT(A2;LEN(A2)-FIND(" ";A2));A2)

In C2: If string start (begins) with specific substring, then replace that with another specific substring

=IF(SUM(COUNTIF(B2;{"Super";"Excellent";"Right";"Perfect";"Great";"Ideal"}&"*"));"WoW "&RIGHT(B2;LEN(B2)-FIND(" ";B2));B2)

Result is very well. Now I try to do the same with nested version,

= IF(SUM(COUNTIF(A2;{"Not";"Bad"}&" "&{"WoW"\"Super"\"Excellent"\"Right"\"Perfect"\"Great"\"Ideal"}&"*"));RIGHT(A2;LEN(A2)-FIND(" ";A2));IF(SUM(COUNTIF(A2;{"Super";"Excellent";"Right";"Perfect";"Great";"Ideal"}&"*"));"WoW "&RIGHT(A2;LEN(A2)-FIND(" ";A2));A2))

And I know logical why is not working, But I don't know how to merge this two IF() in my Excel 2019

enter image description here

Losai
  • 329
  • 2
  • 9

1 Answers1

1

revised - as requested, see here for link to this file

revision v2 - google sheets screenshot of working function at bottom

Countif for your version Excel belongs to a group of 6 functions that split logical criteria into 2 constituents: range & criteria (Office 365 includes min/maxifs to total 8 functions). The consequence of this deals with syntax - specifically, countifs requires a cell range for range arguments (you cannot use an array).

See here & here for further details in this regard.

As such (and as you've prob. come to realise through various attempts), you cannot simply substitute every reference to cell b2 in the 2nd function with fn1 (well, you can, but the result will be erroneous).

You'll need to make a couple of changes:

Firstly - fn2 - whilst the mechanics of sum(countif<>) are appreciable, alter this to something less parsimonious but more robust (and equally 'nifty'):

I've got with the following schema: sum(iferror(match<>,0) as follows (screenshots refer):

=IF(SUM(IFERROR(1*MATCH({"Super","Excellent","Right","Perfect","Great","Ideal"}&"*",B2,0),0)),"WoW "&RIGHT(B2,LEN(B2)-FIND(" ",B2)),B2)

Revised fn2

Next, you'll need to create an array out of the function within cell b2 - I've approached this by wrapping an index function around the entire eqn, then substituted into the first instance B2 is referenced within fn2, to give the shorter version of this soln as follows:

=IF(SUM(IFERROR(MATCH({"Super","Excellent","Right","Perfect","Great","Ideal"}&"*",INDEX(IF(SUM(COUNTIF(A2,{"Not","Bad"}
&" "&TRANSPOSE({"WoW","Super","Excellent","Right","Perfect","Great","Ideal"})&"*")),
RIGHT(A2,LEN(A2)-FIND(" ",A2)),A2),0,0),0),0)),"WoW "&RIGHT(B2,LEN(B2)-FIND(" ",B2)),B2)

Short version of corrected nested IF function

Finally, whilst seemingly unwieldy, I provide the full version (substituting every instance of cell B2 with the index(,0,0) schema (for completeness!):

=IF(SUM(IFERROR(MATCH({"Super","Excellent","Right","Perfect","Great","Ideal"}&"*",INDEX(IF(SUM(COUNTIF(A2,{"Not","Bad"}
&" "&TRANSPOSE({"WoW","Super","Excellent","Right","Perfect","Great","Ideal"})&"*")),
RIGHT(A2,LEN(A2)-FIND(" ",A2)),A2),0,0),0),0)),"WoW "&RIGHT(IF(SUM(COUNTIF(A2,{"Not","Bad"}
&" "&TRANSPOSE({"WoW","Super","Excellent","Right","Perfect","Great","Ideal"})&"*")),
RIGHT(IF(SUM(COUNTIF(A2,{"Not","Bad"}
&" "&TRANSPOSE({"WoW","Super","Excellent","Right","Perfect","Great","Ideal"})&"*")),
RIGHT(A2,LEN(A2)-FIND(" ",A2)),A2),LEN(A2)-FIND(" ",A2)),A2),LEN(IF(SUM(COUNTIF(A2,{"Not","Bad"}
&" "&TRANSPOSE({"WoW","Super","Excellent","Right","Perfect","Great","Ideal"})&"*")),
RIGHT(A2,LEN(A2)-FIND(" ",A2)),A2))-FIND(" ",IF(SUM(COUNTIF(A2,{"Not","Bad"}
&" "&TRANSPOSE({"WoW","Super","Excellent","Right","Perfect","Great","Ideal"})&"*")),
RIGHT(A2,LEN(A2)-FIND(" ",A2)),A2))),IF(SUM(COUNTIF(A2,{"Not","Bad"}
&" "&TRANSPOSE({"WoW","Super","Excellent","Right","Perfect","Great","Ideal"})&"*")),
RIGHT(A2,LEN(A2)-FIND(" ",A2)),A2))

Unwieldy - complete nested version (no B2 references!)

PS - you might also want to correct the spelling of 'Excellent' (I've assumed 'Excelent' is a typo in your fn3 ☺)

revision v2: google sheets screenshot:

Google sheets screenshot - working fn!

JB-007
  • 2,156
  • 1
  • 6
  • 22
  • Thank you for answering. Now I understand why is better sum(iferror(match<>,0), instead of sum(countif(). But the final result [Corrrected] and [Unwieldy] return the same wrong string. Can you provide your file? Please. Maybe I do wrong conversion of puctuation. https://i.stack.imgur.com/u3cT7.png – Losai Mar 23 '22 at 08:26
  • And your [Corrected] formula sometimes refer to B2 instead of A2 https://i.stack.imgur.com/HbGKw.png – Losai Mar 23 '22 at 14:46
  • Have provided onedrive secure link to non-editable version of this file. See revised soln – JB-007 Mar 25 '22 at 18:07
  • @Losai - I thought it went w/ saying that the 'corrected' referred to B2 in instances that were not problematic - and that the unwieldy had 0 references to B2 (in fact, I did say it in my soln)... further, not sure how you can conclude that either 'corrected' or 'unwieldy' produce incorrect results when col E & F match col C (per screenshot, based upon your specific examples in col A)...? In fact I use conditional formatting to this end. Have provided the file as I mention - I converted your semicolons to commas, and the whole '\' thing inside array didn't work for me so changed that to work – JB-007 Mar 25 '22 at 18:17
  • I like this way of formula, but I don't understand why is not working properly in excel 2019. I attach screenshot with your file opened on my terminal, and keeping your conditional formatting https://i.stack.imgur.com/cYIK5.png – Losai Mar 26 '22 at 19:19
  • Realllllllyyy... hmmm. now that is certainy interesting. do you mind sharing your file (or I'll make mine editable - which means someone can go and change it but you've captured the correct answer already I suspect) and you can go and include your formula there? sound OK? Tell me, did you try it without the 'funky' '/' separators (i.e. using my 'nifty' transpose formula which should/will work in your Excel 2019 all the same? (did you change the spelling of 'Excelent' too? ☺ – JB-007 Mar 27 '22 at 22:38
  • I accept any request and, of course, I change spellings. First I download your file from onedrive, then I open it on my terminal with Excel 2019 and your array formula not working there. Then I open the same file in Excel 2021, and it works perfect. Then I sheare your link to another Excel 2019 users, and again in Excel 2019 its not working. I can give a remote access to my terminal to check it in Excel 2019 version. Thank Tou. – Losai Mar 28 '22 at 08:54
  • Hi - not sure about security of terminal - but if you open in Google sheets it works just fine (noting you need to see 'unwieldy' formula as I think I snuck a 'let' function into the 'corrected' version from cell C3 downwards. I'm really surprised this works in your 2021 version but not 2019 (is 2021 even Office 365 compatible? Shouldn't make a difference). Otherwise I'm unsure which functions are not compatible within 2019 (but are in '2021') -- they should all work... (except for 'let' which is specifically Office 365). I provide a screenshot of the google sheets in another revision above. – JB-007 Mar 31 '22 at 00:24