Formula to count if column contains both text

[adam]

I'm using the following formula to sum up text; if the column contains both 'Apple' and 'Orange' only. If the column contains only either of the word I want the sum to be zero.

=COUNTIFS(tblDailyData!E$18:E$10001,">=" & (E$3),tblDailyData!E$18:E$10001,"<=" & (E$3),tblDailyData!F$18:F$10001,"Apple",tblDailyData!$E$18:$E$10001,"<=" & ($E$3),tblDailyData!$G$18:$G$10001,"M")+COUNTIFS(tblDailyData!E$18:E$10001,">=" & (E$3),tblDailyData!E$18:E$10001,"<=" & (E$3),tblDailyData!F$18:F$10001,"Orange",tblDailyData!$E$18:$E$10001,"<=" & ($E$3),tblDailyData!$G$18:$G$10001,"M")

At present if sum ups if the column either contains 'Apple' or 'Orange' or both the words. How could I achieve this?

[HansV]

The part

tblDailyData!E$18:E$10001,">=" & (E$3),tblDailyData!E$18:E$10001,"<=" & (E$3)

says that E18:E10001 should be both >= and <= E3, so it should be equal to E3, And then you mention

tblDailyData!$E$18:$E$10001,"<=" & ($E$3)

another time, that is superfluous. So your current formula can be shortened to

=COUNTIFS(tblDailyData!E$18:E$10001,E$3,tblDailyData!F$18:F$10001,"Apple",tblDailyData!$G$18:$G$10001,"M")+COUNTIFS(tblDailyData!E$18:E$10001,E$3,tblDailyData!F$18:F$10001,"Orange",tblDailyData!$G$18:$G$10001,"M")

But I don't understand your question. Could you provide a simple example - you can omit the conditions on columns E and G.

[adam]

My question is;

I have the words "Apple" and "Orange" in column B of the sheet tblDailyData.

I have date in column A starting from A2 onwards. I have the gender in column C starting from C2 onwards.
I have a single date written in cell E3.

What I want the formula to do is;

to count the total number of "Apple" and "Orange" from column B of the sheet based on the date written in cell E3.

I don't want the formula to count column B, if the column has "Apple" only. in the same way I don't want the formula to count if the column B has "orange" only.

I ONLY want the formula to count if both "Apple and "Orange" is present in column B of the sheet including the gender M in column C.

I hope I've made the question clear.

Note: I have changed the columns in this reply.

[HansV]

Using the ranges from your first post:

=IF(OR(COUNTIFS(tblDailyData!E$18:E$10001,E$3,tblDailyData!F$18:F$10001,"Apple",tblDailyData!$G$18:$G$10001,"M")=0,COUNTIFS(tblDailyData!E$18:E$10001,E$3,tblDailyData!F$18:F$10001,"Orange",tblDailyData!$G$18:$G$10001,"M")=0),"",COUNTIFS(tblDailyData!E$18:E$10001,E$3,tblDailyData!F$18:F$10001,"Apple",tblDailyData!$G$18:$G$10001,"M")+COUNTIFS(tblDailyData!E$18:E$10001,E$3,tblDailyData!F$18:F$10001,"Orange",tblDailyData!$G$18:$G$10001,"M"))

[adam]

Thanks for the help Hans.