Add values based on text color with the criteria of a date

[Peter Gonzalez]

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.

 

[Duke Carey]

Are you using conditional formatting to change the font color?

 

[Peter Gonzalez]

No, I use standard colors to change the font only

 

[Duke Carey]

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]

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]

Sure, that logic can drive both the conditional formatting and the sumproduct

=SUMPRODUCT(LEFT([cells with the code],3)="R33"),--([column with
dates]=[target date]),[column with ordered data])

You'll have to do it again for the the R34 value, or (not tested) you could
use

=SUMPRODUCT(LEFT([cells with the code],3)={"R33","R34"})

Have to leave RIGHT NOW but will check in with you later

 

[Duke Carey]

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*(D26=[cell with desired
date))

 

[Peter Gonzalez]

Thx for the all the help I think I got what I needed

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*(D26=[cell with desired
date))

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