Formula to count occurrences of multiple elements within a string

Hello Jeff,

There might be a formula approach you can adopt but I don't know if it could handle a large amount of data, how many records do you have?

I presume you want the formula to work in Excel 2000 rather than 2007?

Do the records really show numbers 1 to 10 or is that just a simplified example?

I was looking at a formula using MMULT function which is (a) restricted in the amount of data it can handle and (b) not available in Excel 2000 (I think?) but I managed to avoid MMULT by using this formula

=SUM((FREQUENCY(IF(ISNUMBER(FIND(TRANSPOSE(IF(E3:E12,MOD(B3:B12,10))),K3:K22)),ROW(K3:K22)), ROW(K3:K22))>=IF(D14="AND",SUM(E3:E12+0),1))+0)

which needs to be confirmed with CTRL+SHIFT+ENTER

It's still likely to be pretty slow, though, on large datasets......

If you want to add in additional conditions, i.e. to count the above but only when L3:L22 = "x" then you can amend like this

=SUM((FREQUENCY(IF(ISNUMBER(FIND(TRANSPOSE(IF(E3:E12,MOD(B3:B12,10))),K3:K22)),IF(L3:L22="x", ROW(K3:K22))),ROW(K3:K22))>=IF(D14="AND",SUM(E3:E12+0),1))+0)

Note, the above works with 1 to 10 representing the conditions, if you want any more numbers.....or something different like text then that can be done too but the formula will need tweaking.....

Hi

Thanks for your help, I will go away and see what I can do. The numbers 1 to 10 are actually used as in reality each represents a text string that is too long to be used in the record, The total number of records is not that high up to a total of around five thousand.

I generally work in Excel 2000 and then check with other versions to see if there are any conflicts, but so far it seems to work that way.

Once again many thanks I will get back to you to let you know how I get on.

Regards

Jeff

HI

So far it works well but with one or two exceptions.

If no items are flagged as true and the option is AND it displays 21, for OR it displays 0.

Although the example did not show it, it is possible that there is only one condition recorded, however if when evaluating if only one condition is flagged OR displays correctly and AND displays the same value as OR.

If three conditions are flagged AND counts correctly and OR is counting one more than actually exists.

If you have an opportunity to have a look I would appreciate it.

Regards

Jeff

Hello Jeff,

Yes, I tried to use MOD to distinguish between 10 and 1 but I didn't think that through......it won't work. The version below should fix the problems you pointed out.......except you say....

Originally Posted by solnajeff

Although the example did not show it, it is possible that there is only one condition recorded, however if when evaluating if only one condition is flagged OR displays correctly and AND displays the same value as OR.

That seems to be correct to me, unless I'm misinterpreting what you mean by OR and AND. If you pick two numbers, e.g. 5 and 7 I'm assuming that the OR should count all rows which have either a 5 OR a 7, but for AND it will only count rows with both. If you select 1 number then the number of rows for OR and AND are the same, aren't they?

=SUM((FREQUENCY(IF(ISNUMBER(FIND(","&TRANSPOSE(IF(E3:E12,B3:B12))&",",","&K3:K22&",")),ROW(K3:K22)), ROW(K3:K22))>=IF(D14="AND",MAX(1,SUM(E3:E12+0)),1))+0)

Hi

It is probably how I described it, although using and it is a situation where only the flagged criteria are counted. So that means only condition 1 if 1 is the only flagged condition, or only 2 and 3 if they are flagged and so on. I apologise if it was not clear enough.

Regards

Jeff

OK, so let me get that straight....

If you have 5 and 7 selected with AND then you only want to count entries which have 5 and 7 and nothing else?

HI

I have been working on the problem and have come up with a solution that, so far, appears to work.

The values are to be included as follows:
For OR count if any value matches the selected critera.
For AND count if the selected criteria match the values available, this holds true if there are more e.g. four values but two match the selected criteria.

The description would be "count if the set of conditions contains all selected criteria".

I have attached the modified file to illustrate.

Once again thank you for your help. I know there will be a more elegant solution available, but this works and I do not want to take up more of your time than necessary.

Thanks again

Jeff

Hello Jeff,

I'm a little confused now. The last formula I posted, i.e.

=SUM((FREQUENCY(IF(ISNUMBER(FIND(","&TRANSPOSE(IF(E3:E12,B3:B12))&",",","&K3:K22&",")),ROW(K3:K22)), ROW(K3:K22))>=IF(D14="AND",MAX(1,SUM(E3:E12+0)),1))+0)

returns exactly the same results as on your latest example, doesn't it? I know you've changed the layout but if you concatenated the results in a single column, as before, then the results would match (except with your method, when no items are flagged you get 20).

I note that when 1 item is flagged that OR gives the same result as AND

HI

You are absolutely correct, I think I had spent so much time looking at this that I was going round in circles.

I was saying one thing and thinking another.

My apologies and thanks.

Regards

Jeff