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
Cupcake	Yes	Yes	Yes
Brownie	No	No	Yes
Pie	Yes	Yes	No
Fudge	Maybe	No	No
Cake	No	Yes	Maybe
Icing	Maybe	No	No
Cupcake	No	Yes	No

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B050"]Cake[/TD]
[TD="bgcolor: #00B050"]Yes[/TD]
[TD="bgcolor: #00B050"]Yes[/TD]
[TD="bgcolor: #00B050"]No[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B050"]Cake[/TD]
[TD="bgcolor: #00B050"]Yes[/TD]
[TD="bgcolor: #00B050"]Yes[/TD]
[TD="bgcolor: #00B050"]No[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #00B050"]Cupcake[/TD]
[TD="bgcolor: #00B050"]Maybe[/TD]
[TD="bgcolor: #00B050"]Maybe[/TD]
[TD="bgcolor: #00B050"]No[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=SUM(COUNTIFS(B:B,{"Cake","Cupcake"},C:C,"<>No",D:D,"<>No",E:E,"<>Yes"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.