[VBA/Formula] Countifs where Criteria is one of comma separated value in cell?

[RockandGrohl]

Let's say I have a column which can have any single letter or number, and then I want to countif where all characters are "1, 2, 4" or "E, F" or "L, U, V, K"

How can I do this? The cell with the criteria in is in column A and the range is in sheet "LYTemp" range "W2:W & Lastrow"

Thanks.

 

[JustynaMK]

Do you mean something like that?

=SUMPRODUCT(--ISNUMBER(FIND($E$1:$E$14,A1)))

 

[RockandGrohl]

Do you mean something like that?

=SUMPRODUCT(--ISNUMBER(FIND($E$1:$E$14,A1)))

View attachment 3692

I uhm... Well I er... Well.. That's great. I was like elbow deep in a stupid macro to do this and getting nowhere, that's exactly it.

So if it doesn't match 1, 2, 4, E, F or "", how can I count that?

Thanks!

 

[JustynaMK]

Hah I know this feeling Glad it worked!

Do you mean that you would like to display zero in case none of the characters / values match? The formula should already do that for you - see "X, Y, Z":

 

[RockandGrohl]

I mean I'd like it to count anything that isn't in the given arrays, that isn't a blank.

I can't put a chain of characters that can be "other", there's too many, so is there a way to say "If it's not the above, and if it's not blank, then count"?


Oh, also, can this logic be applied to sum a range? Would be very useful, thank you!

 

[RockandGrohl]

Here's my sum logic:

=SUMPRODUCT(--(--ISNUMBER(FIND(LYTemp!$W2:$W4648,A3))),LYTemp!$P$2:$P$4648)

Seems to be working.

Nevermind. One criteria was right, all the others were wrong...

DOUBLE NEVERMIND.

I had the formula wrong - note I haven't locked off two of the values.

 

[JustynaMK]

Did you manage to solve it? or do you still need help?

Edit - if you want to count how many of your characters did NOT appear in your list, you can use the following Array formula (assuming you have access to TEXTJOIN function):

=SUMPRODUCT(--ISERROR(FIND(TRANSPOSE(MID(SUBSTITUTE(A4,", ",""),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE(A4,", ","")))),1)),TEXTJOIN("",,$E$1:$E$14))))
Enter it with Ctrl+Shift+Enter

Using my previous screenshot as an example, the first row will return 1 ("2" is not available), row 2 will show 0 (both E and F are available), row 3 will show 3 (X, Y, Z are not found) and, finally, row 4 will show 1 (only "X" is not found) -

 

[RockandGrohl]

Unfortunately we're on 2010, but I've just gone the lazy way out and took the time to list all the alpha-numeric characters we aren't using, hey, your original suggestion was the best way.

Thanks for the help! I got the sum working with a bit of jiggery-pokery

 

[JustynaMK]

Awesome, glad you solved it take care & have a great weekend

 

[RockandGrohl]

Did you manage to solve it? or do you still need help?

Edit - if you want to count how many of your characters did NOT appear in your list, you can use the following Array formula (assuming you have access to TEXTJOIN function):

=SUMPRODUCT(--ISERROR(FIND(TRANSPOSE(MID(SUBSTITUTE(A4,", ",""),ROW($A$1:INDEX($A:$A,LEN(SUBSTITUTE(A4,", ","")))),1)),TEXTJOIN("",,$E$1:$E$14))))
Enter it with Ctrl+Shift+Enter

Using my previous screenshot as an example, the first row will return 1 ("2" is not available), row 2 will show 0 (both E and F are available), row 3 will show 3 (X, Y, Z are not found) and, finally, row 4 will show 1 (only "X" is not found) -

Hold up,

If this is my formula to sum up column J of "Booking Database":

=SUMPRODUCT(--(--ISNUMBER(FIND('[Regional Press Reporting 2019.xlsm]Booking Database'!$AD$2:$AD$14794,A3))),'[Regional Press Reporting 2019.xlsm]Booking Database'!$J$2:$J$14794)

How do I add another criteria on to it? I have dates in column U in format "dd/mm/yyyy" and I want to add two criteria on my sumproduct that looks for dates between serial 43466 and 43496

Is this possible? Thanks.