yogi_Conditional Formatting For Items Where Every Material Listed Has Been Priced And Used For Open Ended List of Materials

                    Google Spreadsheet   Post  #1774
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-26-2014
post by  robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?

following is a case ...
where I have highlited cells in column D where all listed materials have been priced and used in an item

yogi_Conditional Formatting For Items With Materials Used With No Material Price Listed For Open Ended List of Materials

                    Google Spreadsheet   Post  #1773
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-26-2014
post by  robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?

Ok, the formula you listed 

=and(E3>0,E$1=0)+and(F3>0,F$1=0)+and(G3>0,G$1=0)

will definitely work, and I can change the range to every cell in the column. However, I was hoping there was a way to do this without writing out every and statement for each row (there are over 30). It's a momentary pain, but I was hoping for a more graceful solution, possibly changing it to something like:


=and(E3:G3>0,E$1:G$1=0)I know that way specifically won't work, but is there some way to go about it that I can use the and statement over the range instead of each individually?
-----------------------------------------------------------------------------------------------------------------------------------
following is the solution to a bit more generalized problem using open-ended list of Materials

yogi_Conditional Formatting For Items With Materials Used With No Material price Listed

                    Google Spreadsheet   Post  #1772
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-26-2014
post by  robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?
I'm trying to use Sheets to make a list of items, the materials required to make them, costs of those materials, and calculate a total cost for each item. It's a rather large spreadsheet. I'm using the SUMPRODUCT formula to generate the cost of each item. However, I'd like to format the cost cell to turn red if I've forgotten to input a value for the cost of a material for a given item. I know how conditional formatting works, but I don't know the formula to use. Here's some codefu that's roughly what I'm looking for:

IF(AND(Material > 0, MaterialCost = 0), Red, White)

This works when I use one cell for Material and MaterialCost, however I'd need to check over the entire SUMPRODUCT arrays; in other words, this needs to check if Material1 > 0 and MaterialCost1 = 0, then Material2and MaterialCost2, etc. It would then turn red if any of the checks comes out true. I don't want the cost cell to turn red if that item doesn't require the material whose cost is missing.

I'm not sure how to get the logic formulas to check the entire list and return values without writing each check explicitly, which would be a huge pain in the ass. I've tried using something like Cell1:Cell50 in the logic formula but it doesn't return values.

Any ideas?
---
Alright I've created a test sheet that looks similar to what I'm doing:

https://docs.google.com/spreadsheets/d/1GAGRupvP_EavQ5YHqgsmqMeV_3oQR50r7ltJWGXobLs/edit?usp=sharing

So I've got prices listed in row 2, and Material 2 has no price listed. Therefore any item that requires material two (two and four) has its cost cell turn red, as I've done manually. The actual sheet is about 30x60, so like I said I don't want to type out each manually.

GimeIG's answer is a bit different than I want, it ends up turning the material cell red instead of the cost cell.
----------------------------------------------------------------------------------------------------------------------------