Count certain "x" or numbers in array with SUM(FREQUENCY(IF

[esbencito]

Hi all,

I'm trying to add more criteria to my SUM(IF(FREQUENCY array formula, but struggling to do so. Currently the formula only considers cells with "x"

Please Login or Register  to view this content.

but I want to make it more specific and add below criteria to it. Basically, it should not only count "x", but also " x", "xx", "xxx" or positive numbers such as "2", "5" while excluding zero. It should not count words such as "fox" or "lux" or "-5", "-8" etc.

new criteria:

CODE]=OR(REPT("x",{1,2,3,4,5})=SUBSTITUTE(EG13," ",""),N(EG13)>0)[/CODE]

current FREQUENCY formula that only considers "x"

Please Login or Register  to view this content.

[daddylonglegs]

Try this version

=SUM(IF(FREQUENCY(IF((ISNUMBER(MATCH(SUBSTITUTE($J$13:$J$29," ",""),REPT("x",{1,2,3,4,5}),0))+(ISNUMBER($J$13:$J$29+0)*($J$13:$J$29>0)))*($I$13:$I$29<>""),--$I$13:$I$29),--$I$13:$I$29),1))

confirm with CTRL+SHIFT+ENTER

[esbencito]

That seems to work! Need to do some more testing with my bigger data set tomorrow, but it should be fine... Thanks!!