Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-320149%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320149%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20am%20novice.%20And%20hope%20someone%20can%20help.%3C%2FP%3E%3CP%3EI%20have%20numbers%20in%20the%20range%20of%201%20to%2090%20spread%20in%202%20adjacent%20columns.%3C%2FP%3E%3CP%3ESome%20numbers%20can%20appear%20in%20both%20and%20either%20or%20both%20cells%20can%20be%20empty%3C%2FP%3E%3CP%3EEach%20cell%20can%20have%20up%20to%20about%2010%20entries%20separated%20by%20semicolons%3C%2FP%3E%3CP%3EI%20need%20to%20count%20the%20no%20of%20rows%20where%20a%20number%20appears%20in%20either%20column%20between%20a%20range%20of%20for%20example%2050%20%E2%80%93%2080%3C%2FP%3E%3CP%3EHope%20that%20makes%20sense.%3C%2FP%3E%3CP%3EI%20have%20become%20stuck%20because%20of%20the%20semicolons.%3C%2FP%3E%3CP%3EFor%20example%3C%2FP%3E%3CP%3E20%3B33%3B55%3B79%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B1%3B20%3B55%3C%2FP%3E%3CP%3E4%3B54%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E37%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2063%3B67%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2029%3B35%3B45%3C%2FP%3E%3CP%3E11%3B43%3B44%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2045%3B49%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENumber%20of%20rows%20%3D3%3C%2FP%3E%3CP%3E%26nbsp%3BIs%20there%20a%20formula%20you%20could%20give%20me%20or%20can%20you%20point%20me%20in%20the%20right%20direction%20of%20how%20to%20do%20it%20myself%3F%3C%2FP%3E%3CP%3EThanks%20for%20any%20help.%3C%2FP%3E%3CP%3EGlenn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-320149%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320726%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320726%22%20slang%3D%22en-US%22%3E%3CP%3EIt's%20hard%20to%20say%20what%20is%20the%20best%2C%20that's%20always%20a%20combination%20and%20depends%20on%20situation.%20There%20are%20a%20lot%20of%20resources%2C%20small%20part%20is%20mentioned%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FResources-and-Community%2FLittle-list-of-some-cool-Excel-websites%2Fm-p%2F4893%23M8%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FResources-and-Community%2FLittle-list-of-some-cool-Excel-websites%2Fm-p%2F4893%23M8%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMicrosoft%20support%20pages%20are%20better%20if%20to%20understand%20how%20this%20or%20that%20function%20works.%20For%20the%20patterns%20google%20helps%20to%20find%20them%2C%20I%20guess%2099%25%20of%20questions%20we%20have%20are%20already%20solved%20by%20someone%2C%20very%20often%20with%20explanations%20and%20variants%20of%20the%20solution.%20In%20a%20while%20you%20understand%20which%20resources%20are%20most%20suitable%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320666%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320666%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3EJust%20tried%20it%20out%20and%20works%20perfectly.%20Thank%20you%20very%20much%20for%20the%20formula%20and%20speedy%20response.%20Have%20decided%20to%20gain%20a%20better%20understanding%20of%20excel%20and%20i%20guess%20the%20best%20place%20to%20start%20would%20be%20the%20Microsoft%20site%20with%20YouTube%20as%20a%20back%20up.%20Thank%20you%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320229%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320229%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20easier%20to%20do%20with%20Power%20Query%2C%20but%20if%20with%20formula%20that%20could%20be%20for%20data%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20412px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68914i4D81C80E3945B4BD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewe%20could%20add%20helper%20column%20C%20with%20formula%3C%2FP%3E%0A%3CPRE%3E%3D(SUMPRODUCT((VALUE(%0A%20%20%20TRIM(MID(SUBSTITUTE(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1)%2C%22%3B%22%2CREPT(%22%20%22%2C99))%2C%0A%20%20%20%20%20%20%20%20%20(ROW(OFFSET(%24A%241%2C%2C%2CLEN(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1))-LEN(SUBSTITUTE(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1)%2C%22%3B%22%2C%22%22))%2B1))-1)*99%2B%0A%20%20%20%20%20%20%20%20%20((ROW(OFFSET(%24A%241%2C%2C%2CLEN(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1))-LEN(SUBSTITUTE(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1)%2C%22%3B%22%2C%22%22))%2B1)))%3D1)%2C99)))%20%26gt%3B%24F%241)*%0A%20%20%20(VALUE(%0A%20%20%20TRIM(MID(SUBSTITUTE(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1)%2C%22%3B%22%2CREPT(%22%20%22%2C99))%2C%0A%20%20%20%20%20%20(ROW(OFFSET(%24A%241%2C%2C%2CLEN(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1))-LEN(SUBSTITUTE(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1)%2C%22%3B%22%2C%22%22))%2B1))-1)*99%2B%0A%20%20%20%20%20%20((ROW(OFFSET(%24A%241%2C%2C%2CLEN(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1))-LEN(SUBSTITUTE(IF(ISBLANK(A1)%2C%220%22%2CA1)%26amp%3B%22%3B%22%26amp%3BIF(ISBLANK(B1)%2C%220%22%2CB1)%2C%22%3B%22%2C%22%22))%2B1)))%3D1)%2C99)))%20%26lt%3B%24F%242))%0A%20%20%26gt%3B0)*1%3C%2FPRE%3E%0A%3CP%3Ewhere%20TRIM%20part%20converts%20combination%20of%20two%20cells%20into%20array.%26nbsp%3BWhen%20we%20compare%20first%20TRIM%20with%20min%20range%2C%20second%20one%20with%20max%20range%2C%20multiplication%20gives%20AND%20condition%20and%20SUMPRODUCT%20calculates%20how%20many%20numbers%20within%20the%20string%20are%20in%20the%20range.%20Finally%20return%201%20if%20more%20than%20one%20or%20zero%20otherwise.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20that%20only%20to%20SUM%20all%20cells%20in%20columns%20C.%3C%2FP%3E%0A%3CP%3ESee%20details%20in%20attached%20file.%20Power%20Query%20variant%20is%20also%20here%20(second%20sheet).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi

I am novice. And hope someone can help.

I have numbers in the range of 1 to 90 spread in 2 adjacent columns.

Some numbers can appear in both and either or both cells can be empty

Each cell can have up to about 10 entries separated by semicolons

I need to count the no of rows where a number appears in either column between a range of for example 50 – 80

Hope that makes sense.

I have become stuck because of the semicolons.

For example

20;33;55;79                            1;20;55

4;54                                          

37                                              63;67

                                                   29;35;45

11;43;44                                     45;49

 

Number of rows =3

 Is there a formula you could give me or can you point me in the right direction of how to do it myself?

Thanks for any help.

Glenn

3 Replies
Highlighted

Hi,

 

It's easier to do with Power Query, but if with formula that could be for data like this

image.png

we could add helper column C with formula

=(SUMPRODUCT((VALUE(
   TRIM(MID(SUBSTITUTE(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1),";",REPT(" ",99)),
         (ROW(OFFSET($A$1,,,LEN(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1))-LEN(SUBSTITUTE(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1),";",""))+1))-1)*99+
         ((ROW(OFFSET($A$1,,,LEN(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1))-LEN(SUBSTITUTE(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1),";",""))+1)))=1),99))) >$F$1)*
   (VALUE(
   TRIM(MID(SUBSTITUTE(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1),";",REPT(" ",99)),
      (ROW(OFFSET($A$1,,,LEN(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1))-LEN(SUBSTITUTE(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1),";",""))+1))-1)*99+
      ((ROW(OFFSET($A$1,,,LEN(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1))-LEN(SUBSTITUTE(IF(ISBLANK(A1),"0",A1)&";"&IF(ISBLANK(B1),"0",B1),";",""))+1)))=1),99))) <$F$2))
  >0)*1

where TRIM part converts combination of two cells into array. When we compare first TRIM with min range, second one with max range, multiplication gives AND condition and SUMPRODUCT calculates how many numbers within the string are in the range. Finally return 1 if more than one or zero otherwise.

 

After that only to SUM all cells in columns C.

See details in attached file. Power Query variant is also here (second sheet). 

Highlighted

Hi Sergei

Just tried it out and works perfectly. Thank you very much for the formula and speedy response. Have decided to gain a better understanding of excel and i guess the best place to start would be the Microsoft site with YouTube as a back up. Thank you again.

 

Highlighted

It's hard to say what is the best, that's always a combination and depends on situation. There are a lot of resources, small part is mentioned here https://techcommunity.microsoft.com/t5/Resources-and-Community/Little-list-of-some-cool-Excel-websit...

 

Microsoft support pages are better if to understand how this or that function works. For the patterns google helps to find them, I guess 99% of questions we have are already solved by someone, very often with explanations and variants of the solution. In a while you understand which resources are most suitable for you.