Thread: Countifs with reference String along with criteria

Hi,

I require to count the no of times a String is repeated - but want it to be referenced to a cell along with a criteria.

Ex: I want to count the no of times the string occurs with another criteria


Sheet1
A B
1 Apple 1234
2 Grape 1235
3 Pine 1236
4 Peach 1237


Sheet2
C D
1 1234 Apple,Orange,Pine
2 1235 Apple,Grapes,Pine
3 1236 Apple,Peach,Grapes
4 1237 Pine,Peach,Grapes
5 1234 Apple,Peach,Grapes
6 1235 Apple,Grapes,Pine
7 1235 Apple,Grapes,Pine
8 1236 Apple,Peach,Grapes


I want to count the no of times Sheet1B1 occurs in Sheet2 ColumnC but at the same time ColumnD does not contain the string in Sheet1A1 for each row

in other words i want to count the no of times "1234" is repeated in Column C where corresponding D does not contain the string "Apple"

Hi

In sheet1 C1 copied down

COUNTIFS('Sheet2'!C:C,B1,'Sheet2'!D:D,"<>*"&A1&"*")

Regards