Counif in vertical list for 2 creterias looking for partial and full text inside cell

Hello Friends

In Column A if contains cells E4:E8 (i.e., AAA, BBB or GGG) anywhere inside cell and in Column B if it contains Cells F4:F8 (i.e., X and Y) then need to count in light yellow cells H4 by using formula.

thanks in advance.

Instead of writing seperate formulas and summing is there any single formula to count as 43.

Try this:

=SUM(COUNTIFS($A$1:$A$200,"*"&E4:E9&"*",$B$1:$B$200,F4:F9))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

This array formula shows result as 22 but it should be 43 even after CTRL+SHIFT+ENTER.

Why should it be 42? There are 14 matching the first condition, 8 matching the second and no matches for the third - that makes 22. Is the lookup value missing for the third condition in the workbook?

Excel 2016 (Windows) 32 bit

	E	F	G	H	I	J	K
4	AAA	X		43	Result by formula		14
5	BBB	Y					8
6	GGG						0
7				Need to be counted
8

Sheet: Sheet1

Try

=SUMPRODUCT(COUNTIFS(A2:A201,"*" &E4:E6 &"*",B2:B201,"X"))+SUMPRODUCT(COUNTIFS(A2:A201,"*" &E4:E6&"*",B2:B201,"Y"))

I think the result is 56 if I have interpreted your request correctly.

No the requirement is if the column A cells contains anywhere `AAA' or `BBB' or `GGG' AND (Not OR) the Column B cells contains `X' or `Y' then need to be counted per light green cells.

For GGG in Column A and for X or Y in Column B also need to be counted which is omitted. Also for `AAA' Y should be counted, for `BBB' `X' should be counted.

Ah, I see, John! Yes, I think you've interpreted it correctly.

I get the same as John with this:

Array entered.

Formula:

Please Login or Register  to view this content.

with an helper column,

C1

Formula:

Please Login or Register  to view this content.

H4

Formula:

Please Login or Register  to view this content.

Please see post #3:

Instead of writing seperate formulas and summing is there any single formula to count as 43.

John I prefer yoursas:

1) it also works with non-standard strings (e.g. starting with an extra digit)
2 It's non-array entered
3) It can be simplified (I think...) to

=SUMPRODUCT(COUNTIFS($A$1:$A$201,"*" &$E$4:$E$6 &"*",$B$1:$B$201,$G$4:$G$5))

That returns 0 here, Glenn.

Hello

thanks to all

John (Non Array), Dave (Array) and Ankur (Non Array with helper) formulas working well.

Glenn, the formula shows result as 22 but it should be 56.


thanks again

@ Glenn

Good point on the non-standard strings RE: extra digits.

I'll sleep on it.

I pared it down to about 20 rows (easier to follow in evaluate formulae) and it seemed OK.. There was a mistake in the formula that I posted above: it does indeed return 22.

=SUMPRODUCT(COUNTIFS($A$1:$A$201,"*" &$E$4:$E$6 &"*",$B$1:$B$201,$F$4:$F$5))

I'll ruminate on this later...

The OP is probably Ok with the solution. However, I'm a bit stubborn so I played on with this for a bit...

On this (pared down) sheet, the various formulae have their strengths and weaknesses, but this seems to work with variable string formats and is nice and short. It is, however, array entered:

=SUMPRODUCT(COUNTIFS($A$2:$A$20,"*" &$E$5:$E$7 &"*",$B$2:$B$20,TRANSPOSE($G$5:$G$6)))

i think it's OK...

Originally Posted by Glenn Kennedy

The OP is probably Ok with the solution. However, I'm a bit stubborn so I played on with this for a bit...

You and me both. Made this one trying to get to sleep ... LOL

Array entered ... takes care of any variable prefixes. If the "-" varies that will be a new challenge.

=SUM((RIGHT(REPLACE(A2:A20,FIND("-",A2:A20),99,""),3)=TRANSPOSE(E5:E7))*COUNTIF(G5:G6,B2:B20))