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

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

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.``  Register To Reply

2. ## Re: Count certain "x" or numbers in array with SUM(FREQUENCY(IF

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  Register To Reply

3. ## Re: Count certain "x" or numbers in array with SUM(FREQUENCY(IF

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

There are currently 1 users browsing this thread. (0 members and 1 guests) 