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.

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.

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.

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.

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.

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

"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.

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" 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.

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*(D26=[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.