Excel/mixing equations

Advertisement


Question
I use colored cells in my spreadsheet to identify a type of cost. To total the non-colored cells I use: =ColorFunction(L216,L126:L216,TRUE)  [NOTE* L216 is a non-colored cell] This equation work fine and give me a total of all non-colored cells. The next function I need to have performed is this: I have a column which cells are full or empty.  I also want to add all cells in column L only when a cell in column O is not empty. For this I use the following equation:
=SUMIFS(O126:O216,O126:O216,"<>",L126:L216,"<>")  [NOTE* this equation works on its own as well.  It equation does its job separately but I need to combine them to perform both functions which will give me a total in colomn L for only the non-colored cells that also have the same amount in column O.  I hope you can help and thanks in advance for your efforts.

Answer
Sorry, Sue, but I follow several specific rules in data control and analysis.  One of them is "color is not data".

Color is a human-eye thing and I have never seen an instance where the colors could not be replaced by real data that can be analysed in a normal way.  If you want to see why I don't do it, look at your very situation.

A human decides he's going to color cells yellow for a certain reason.  A better choice would be to actually put the word YELLOW in a new column for that same row of data.  Best of all would be to put a code in that column instead that actually depicts what this is really about... say REGION1, or REGION2, or PAST DUE, or something other than a color!

Once you have columns that depict all the real datapoints for each row of data, your normal Excel functions can analyze them easily and with no fuss.

Your SUMIFS() function could easily add another group to check the REGION1, right? Or if you insist on the non-specific color categories, you could add a check for the word "Yellow" in the column.

This is my first best suggestion to you.  I do not use actual colors in the database portion of my sheet design, I use it only on the final "reports" section I create FROM the raw data, and even then only on rare occasions.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.