Conditional formatting for cells used in function
Dear all, i would like toask for help with following:
I have calculation for costs of the building construction, where in rows are parts of construction in one cell and prices in next cell. F.e. walls: 100 EUR, roof: 200 EUR and door type 1: 300 EUR and door type 2: 400 EUR. At the end of the list I count price of items used in particular construction - f.e. walls, roof and door type 1 (i do not calculate "door type 2"). Is there any way, how to higlight cells, which where used in my formula - in this case 100 EUR, 200 EUR and 300 EUR value? List is long and I would like to see which items are calculated and which not (without clicking on cell with formula)
I watched several tutorials, but did not find answer. Thanks!
Stan
UPDATE: Thanks to one post I realise I could make it much easier and do it othe way around - first highlight items and then count them. I add file with first idea, which is probably too complicated, so i would do it second way. If some of you would find how to find fuction to count price based on selected items without "side calculation" column would be big help, thanks! C:\fakepath\Sample.xls
How does you formula look like(or how do you select, which items to be included in total costs and which should not )? Could you upload a short sample file showing your structure and method of calculation?
Thanks, i added file.
Stan, are there two separate problems?
For (1), if there's a fixed rule for which items you mark - for instance, if you want to select all of the "wall type 1" items from your list, and never "wall type 2," you can make formulas to do that:
if item=wall type 1, then ___
. But if there's no fixed rule, you can't make the spreadsheet do it automatically.[Edit: see Lupp's solution below, using SUMIF()] For (2), it seems you don't want the extra column, the side calculation. But that might be the easiest way. If you try to put calculations for many rows into one formula in one cell (your "total" cell), you'll have a very long formula. That will be hard to write, maintain, and debug.
An option might be to ...(more)
You was right - there were 2 issues and you was right, Lupp got it right. Thanks!