Countifs?

[JulieQ]

So, I thought I was aweseome & and had a solution, but I don't.... I ended up doubling my count...

What I'm trying to do/say:

Determine all of the WINDOWS (column A), that are 2015 (column B), if DOG or CAT exists in column C.

This does not work, because it doubles my numbers:

Please Login or Register  to view this content.

I would manually filter in excel as follows:
Column A - Windows
Column B - 2015
Column C - Text Filter: Contains dog OR Contains cat

Using the example data below, the answer would be 5 (not 8, like my SUM statement above)


Example data below -

Please Login or Register  to view this content.

[mcmahobt]

COUNTIFS is inherently an "AND" function, not necessarily an "OR" function. You are correct when using SUM to add in an "OR" criteria, but the double counts are stemming from the cells containing multiple values, which Excel doesn't like. If you can't alter your data in such a way that each animal is assigned their own cell and/or column, VBA may be your best bet with this.

[JulieQ]

Thanks mamahobt - Altering the data is not an option (I'm receiving a report) and I'm linking to the data.

[AlKey]

Maybe with a helper column

=SUM(COUNTIFS($A$2:$A$17,D1,$F$2:$F$17,E1,$C$2:$C$17,"*"&{"cat","dog"}&"*"))

Row\Col	A	B	C	D	E	F
1	Server	Year	Input	Windows	2014	Year
2	Windows	2/2/2015	dog		4	2015
3	Unix	2/2/2015	cat			2015
4	Windows	2/2/2015	fish			2015
5	Windows	2/2/2015	dog, cat			2015
6	Windows	2/2/2015	cat			2015
7	Windows	2/2/2015	fish, cat,dog			2015
8	Windows	2/2/2015	cat			2015
9	Unix	2/2/2015	fish, cat,dog			2015
10	Windows	2/2/2015	fish			2015
11	Unix	2/2/2015	fish			2015
12	Windows	1/1/2014	dog, cat			2014
13	Unix	2/2/2015	dog			2015
14	Windows	2/2/2015	cat			2015
15	Unix	2/2/2015	fish, dog			2015
16	Windows	1/1/2014	fish, dog			2014
17	Windows	1/1/2014	dog			2014

Or without helper column

=SUM(COUNTIFS($A$2:$A$17,D1,$B$2:$B$17,">=1/1/2014",$B$2:$B$17,"<=12/31/2014",$C$2:$C$17,"*"&{"cat","dog"}&"*"))