| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add values based on text color with the criteria of a date
Thx for the all the help I think I got what I needed
"Duke Carey" wrote: A quick test shows this works =SUMPRODUCT(--(LEFT(A2:A6,3)={"R33","R34"})*B2:B6) If you want to incorporate the date then =SUMPRODUCT(--(LEFT(A2:A6,3)={"R33","R34"})*B2:B6*(D2:D6=[cell with desired date)) "Peter Gonzalez" wrote: I determine the color by the the first the letter and 2 digits say from my example R33 and R34 are Black S12 and S14 are Red and anything other than that Is Blue Can I make it so that in the column next to the values if it recognizes any of the R33, R34 etc. Is there a formula that would automatically input the determined color "Duke Carey" wrote: Well then, I'd recommend you start by adding a column that contains a code for each row that indicates how you are determining the color. The code could even be the color itself, i.e., Red, Black, Blue. You could use those codes BOTH 1) to trigger conditional formatting and 2) to facilitate the summary you want. The summary would simply use SUMPRODUCT()s to ID the date, the color code (the new column's values), and to then sum either the Completed column of the Ordered column Any other solution involves VBA. Here's a link to Chip Pearson's article about SORTING by color. The code there can be adapted to SUMming, if you want http://www.cpearson.com/excel/SortByColor.aspx "Peter Gonzalez" wrote: No, I use standard colors to change the font only "Duke Carey" wrote: Are you using conditional formatting to change the font color? "Peter Gonzalez" wrote: I have some data that I gather up every day. This data concerns of Parts, Quantities and work orders numbers. The parts have to be divided by departments although I can never predict which parts are going to show up on my data report nor how many. So what I do is divide the parts I get by text color Red, Blue, Black. So what I wanted know was if there is a formula that I can use to add up the values I get in Red, Blue, Black separately on to a different sheet that I have set up for the totals. My data sheet looks like this: (Example) Column C Column D Column E Column G Item # Qty. Ordered Qty. Completed Date K20206-3731 288 (Blue) 283(Blue) 4/9/2010 R33943 1133(Black) 1065(Black) 4/9/2010 S14185 60(Red) 0(Red) 4/9/2010 S21747 567(Blue) 505(Blue) 4/9/2010 R34108 881(Black) 838(Black) 4/9/2010 S12029 1065(Red) 1061(Red) 4/9/2010 etc. I want my totals Sheet to look like this: Dept. A Dates Qty. Ordered Qty. Completed 4/9/2010 Red total Red total Dept. B Dates Qty. Ordered Qty Completed 4/9/2010 Blue Total Blue Total Dept. C Dates Qty. Ordered Qty Completed 4/9/2010 Black Total Black Total I get hundreds of parts and quantities every day so it kind of is time consuming. Please help me and make my life easier. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I add the values based the color of font and the date | Excel Worksheet Functions | |||
| color a range of cells based on a criteria | Excel Discussion (Misc queries) | |||
| Cond. format text color based on another's text color | Excel Discussion (Misc queries) | |||
| list two columnar values based on conditional formating (text color) | Excel Worksheet Functions | |||
| Copy Color Formats Based On Column Date Values | Excel Discussion (Misc queries) | |||
![Peter Gonzalez[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
