• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

conditional formatting

A

ahhhmed

Member
Hi,

I formatted the cells in Column (C) according to certain values in column (A). Formatting was by changing cell fill. Now I want to calculate the values in column (B) which correspond to cells in column (C), but I want to exclude the colored cells from calculation. Notice that the conditional formatting changes by the change of values in column (A).


Any ideas for a good formula?
 
Ahhhmed


Firstly welcome to the Chandoo.org Forums


Have you Formatted the Cells in Column C or Conditionally Formatted them?

Because there is a big difference
 
Hi,

Thanks for the reply.

I formatted the cells in C conditionally according to the values in A. When the values in A change, the color of C cells change. I want to exclude those colored cells in C from calculation.


For conditional formatting I used the New rule choice and I went to (Use a formula to determine which cells to format) then I wrote =($C1="XXX").
 
What about using the same criteria in your sum

e.g. =SUMIFS(B2:B100,C2:C100,"<>xxx")

that is Sum Column B except where Col C = xxx
 
Thank you for that.

What I really want to do is to count the cells in B which are more than 0 and less than 7 except where col C = xxx. I tried this


=COUNT(b13:b38,c13:c38, "< > xxx")-COUNTIF(b13:b38,"<0.1")-COUNTIF(b13:b38,">7")

It did not work,

I tried


=COUNTIFS(b13:b38,c13:c38, "< > xxx")-COUNTIF(b13:b38,"<0.1")-COUNTIF(b13:b38,">7")

and it did not work.


It is a good challenge.
 
=SUMPRODUCT((B13:B38>=0)*(B13:B38<=7)*(C13:C38<>"xxx"))
 
Retry

I just changed the formula as I realised I made a mistake
 
If you have blanks you'll need to change to

=SUMPRODUCT((B13:B38>=0)*(B13:B38<=7)*(C13:C38<>"xxx")*(B13:B38<>""))

or

=COUNTIFS(B13:B38,">="&0,B13:B38,"<="&7,C13:C38,"<>"&"xxx")
 
Thanks Hui, The second formula worked well. The first not.


Now what about if column C has two conditions xxx and yyy? Can I use


=COUNTIFS(B13:B38,">="&0,B13:B38,"<="&7,C13:C38,"<>"&"xxx","yyy")

?
 
=COUNTIFS(B13:B38,">="&0,B13:B38,"<="&7,C13:C38,"<>"&"xxx",C13:C38,"<>"&"yyy")
 
You must log in or register to reply here.
Back
Top