COUNTIFS for multiple text criteria

**lallo.jr** · 05-21-2010, 08:14 AM

Formula stumped again!

I'd like to count cells only if they contain certain words in no specific order.
dha, gluco,GSE and multi
These cells are not sequential going down because the cells in between should be counted as long as they have dha, gluco and GSE in them

The formula I came up with seems really cumbersome
=COUNTIFS(J4,J6,J8,J10,J12,J14,J16, "dha",J4,J6,J8,J10,J12,J14,J16,"multi",J4,J6,J8,J10,J12,J14,J16,"gluco",J4,J6,J8,J10,J12,J14,J16,"gse") ... etc.

I'd like some help writing the correct and perhaps a more parsimonious formula.

Thanks as always

**daddylonglegs** · 05-21-2010, 08:35 AM

You can't list cells like that for the range criteria in COUNTIFS......but you can use OFFSET to generate those cells, i.e.

=SUM(COUNTIF(OFFSET(J4,{0,2,4,6,8,10,12},0),{"dha";"gluco";"GSE";"multi"}))

**lallo.jr** · 05-21-2010, 08:45 AM

Could you explain to me how that array works? After inserting the formula it still did not detect that text in the cell to return a "1".

I have attached the file; sheet 2 is the one I'm working on.

I also wanted to add to the criteria so that for the day to be counted, both am and pm criteria must be met. (AM must have dha, gluco, GSE, and multi; PM must have gluco, GSE, and dha)

**DonkeyOte** · 05-21-2010, 08:54 AM

It's not clear if you the terms of interest could be embedded within other terms or not... if not (ie mutually exclusive) then:

Please Login or Register  to view this content.

Note: that AM in Col A can be used to differentiate between rows 4 & 5, 6 & 7 etc...

If not mutually exclusive then you will need to revert to SUMPRODUCT / Array so as to append the main strings such that you have a common delimiter encompassing all terms within any given string (searching for delimitertermdelimiter)

**daddylonglegs** · 05-21-2010, 09:25 AM

Sorry,

The formula I suggested was just based on your formula, without seeing your attachment I didn't get the full picture. Looks like DonkeyOte has it covered......

**lallo.jr** · 05-21-2010, 12:25 PM

Originally Posted by DonkeyOte

It's not clear if you the terms of interest could be embedded within other terms or not... if not (ie mutually exclusive) then:

Please Login or Register  to view this content.

Note: that AM in Col A can be used to differentiate between rows 4 & 5, 6 & 7 etc...

If not mutually exclusive then you will need to revert to SUMPRODUCT / Array so as to append the main strings such that you have a common delimiter encompassing all terms within any given string (searching for delimitertermdelimiter)

Using the same formula as above I made a cell for AM and one for PM and then added them into a third. That works for me.