[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]

You should be able to do it by separating your conditions using comma - in general:
=SUMPRODUCT(1st condition,2nd condition,3rd condition)

In your case it would be something like that:
=SUMPRODUCT(your ISNUMBER formula, --($U$1:$U$14>43466), --($U$1:$U$14<43496))

 

[RockandGrohl]

You should be able to do it by separating your conditions using comma - in general:
=SUMPRODUCT(1st condition,2nd condition,3rd condition)

In your case it would be something like that:
=SUMPRODUCT(your ISNUMBER formula, --($U$1:$U$14>43466), --($U$1:$U$14<43496))

Hey,

I have this so far:

=SUMPRODUCT((RPR!$J$2:$J$14794)*(--ISNUMBER(FIND(RPR!$W$2:$W$14794,A3)))*(RPR!$U$2:$U$14794>=Home!A20)*(RPR!$U$2:$U$14794<=Home!A21))

It's giving me £630,675 where I would expect £215,830

In Home cells A20 and A21 I have 01/01/2019 and 31/01/2019 respectively.

Cheers

EDIT: I've just found out, £630,675 is the total revenue for the month across all product lines, it's not looking at revenue for 1, 2 4 etc and for the other entries (3,6 | E,F etc) it's showing 0.

So it looks like it's not going into the product lines...

 

[JustynaMK]

Unfortunately, it's hard to say what is wrong without the screenshots of your dataset. Your current formula works fine for me - see below:

Here's RPR tab - dates between 1/1/19 and 31/1/19 are highlighted with a blue colour. My condition (1, 2, 4) means that only rows 7 & 8 will be ultimately taken into account:

...and here's the Home tab - with your dates in cells A20 & A21, criteria in cell A3 (1, 2, 4) and your formula in cell B1 returning the correct result (6 + 7 = 13):

 

[RockandGrohl]

Yeah I must've had something funny going on.... This totally works, thanks again for all your help.

Old Formula:

=SUMPRODUCT((RPR!$J$2:$J$14794)*(--ISNUMBER(FIND(RPR!$W$2:$W$14794,A3)))*(RPR!$U$2:$U$14794>=Home!$A$20)*(RPR!$U$2:$U$14794<=Home!$A$21))

New Formula:

=SUMPRODUCT((RPR!$J$2:$J$14794)*(--ISNUMBER(FIND(RPR!$AD$2:$AD$14794,Home!A3)))*(RPR!$U$2:$U$14794>=Home!A20)*((RPR!$U$2:$U$14794<=Home!A21)))

Ah, looks like I was missing the "Home" designation on some tags, which was probably looking in the wrong sheet.

 

[JustynaMK]

Glad it's all sorted now! take care

 

[RockandGrohl]

Here's one of the resulting formulas, transposed to VBA, where I sum the pax against a sum of the people who made their first booking, across two databases... It got a bit ridiculous...

Range("U3:U10").FormulaR1C1 = "=(SUMPRODUCT(('[" & bd2.Parent.Name & "]Booking Database'!R2C3:R" & LastrowBD2 & "C3)*(--ISNUMBER(FIND('[" & bd2.Parent.Name & "]Booking Database'!R2C30:R" & LastrowBD2 & "C30,Home!RC[-6])))*('[" & bd2.Parent.Name & "]Booking Database'!R2C21:R" & LastrowBD2 & "C21>=" & TMRS & ")*('[" & bd2.Parent.Name & "]Booking Database'!R2C21:R" & LastrowBD2 & "C21<=" & TMRE & ")*('[" & bd2.Parent.Name & "]Booking Database'!R2C8:R" & LastrowBD2 & "C8=""Y""))+SUMPRODUCT(('[" & bd.Parent.Name & "]Booking Database'!R2C3:R" & LastrowBD & "C3)*(--ISNUMBER(FIND('[" & bd.Parent.Name & "]Booking Database'!R2C30:R" & LastrowBD & "C30,Home!RC[-6])))*('[" & bd.Parent.Name & "]Booking Database'!R2C21:R" & LastrowBD & "C21>=" & TMRS & ")*('[" & bd.Parent.Name & "]Booking Database'!R2C21:R" & LastrowBD & "C21<=" & TMRE & ")*('[" & bd.Parent.Name & "]Booking Database'!R2C8:R" & LastrowBD & "C8=""Y"")))/RC17"