CountIf

[chocolatelove]

I am trying to populate a unique count of the following conditions

Column B is either a Cake or Cupcake
Column C is not equal to No
Column D is not equal to No
Column E is not equal to Yes

If all the above is true, I want my cell to display a count of people who satisfy all the above.

So far, I have
=COUNTIFS(C6:C43, "<>No",D6:D43,"<>No",B6:B43, "Developer"&"")

I tried including the column E and adding another B column condition, but I just get a 0 which doesn't reflect the current count.

Any suggestions or any other ways to do this without a trailing CountIfs?

BONUS: in addition to the count number, I would love to highlight the cells in which all the conditions are met.

thank you

 

[Jonmo1]

Welcome to the board.

If you wanted to count the ones in B that are Cake OR Cupcake, why did you put "Developer" as the criteria??

Is column B a 1 of 3 possible value, Cake Cupcake or Developer ? And therefor you'd say NOT Developer ?
Try
=COUNTIFS(B6:B43,"<>Developer",C6:C43,"<>No",D6:D43,"<>No",E6:E43,"<>Yes")

 

[Eric W]

I'm also a bit confused about the "Developer" in your sample formula.

Here's what I came up with:

=SUM(COUNTIFS(B:B,{"Cake","Cupcake"},C:C,"<>No",D:D,"<>No",E:E,"<>Yes"))

And as far as the Conditional Formatting, you can do this:

Excel 2010

	A	B	C	D	E	F
1
2		Cake	Yes	Yes	No	3
3		Cupcake	Yes	Yes	Yes
4		Brownie	No	No	Yes
5		Pie	Yes	Yes	No
6		Fudge	Maybe	No	No
7		Cake	No	Yes	Maybe
8		Cake	Yes	Yes	No
9		Icing	Maybe	No	No
10		Cupcake	No	Yes	No
11		Cupcake	Maybe	Maybe	No
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula F2 =SUM(COUNTIFS(B:B,{"Cake","Cupcake"},C:C,"<>No",D:D,"<>No",E:E,"<>Yes")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Select columns B:E and use this as the CF formula:

=AND(OR($B1="Cake",$B1="Cupcake"),$C1<>"No",$D1<>"No",$E1<>"Yes",B1<>"")

 

[Pecan]

I approached the problem a little differently...

Based on your information, I placed the formula below in column E and copied to to each row in the body of the table.

=IF(AND(OR(B2="Cake", B2="Cupcake"), C2="No", D2="No", E2="Yes"), 1, "")

This basically indicates which row meets the conditions.

Then I created a Conditional Format (Use a formula to determine...) using: =$F2=1 and selected a fill color. I then applied it to the information in the body of the table (=$B$2:$F$37).

I hope this helps.

Pam

 

[chocolatelove]

Actually, sorry for the throwoff, the developer is a typo, If you replace that with Cake, it makes more sense. I am going to try the answers out and see how they work! Thank you all.