Is there a formula or function that will add the number of hours (by row)
based on category. Meaning if I create another worksheet and had the
information put into these cells can I get it to add together the number of
hours based on the category by row and give me a total number of hours by
category.

MONDAY TUESDAY
A2= Name B2= $$ C2= Cat D2= Hours E2= Cat F2= Hours
A3= C3= Cat D3= Hours E3= Cat F3= Hours
A4= Name B4= $$ C4= Cat D4= Hours E4= Cat F4= Hours
A5= C5= Cat D5= Hours E5= Cat F5= Hours
A6= Name B6= $$ C6= Cat D6= Hours E6= Cat F6= Hours
A7= C7= Cat D7= Hours E7= Cat F7= Hours

The reason for the blank spaces is that generally 1-person does 2 different
categories per day and therefore they general have say 4 hours at cat1 and 4
hours at cat2 just as an example. In addition, in the cells where I have
indicated $$ there would be a dollar amount there like $15.00.

Lets say the other worksheet looked like this:

A1= CAT 1
A2= SUM OF ANY CAT1 HOURS FOR THIS ROW, AND FOR THE ROW DIRECTLY BELOW IT
(A3)
A3= B2 $$ * SUM OF CAT HOURS FROM ROW DIRECTLY ABOVE

This same scenario would need to go into the next columns for however many
categories (generally not more then 6) and it would need to accomplish the
same thing but for the different categories. I.E. CAT2 etc. Then be able to
go for as many as 20 rows down, and then making a total row with total hours
and dollars by category.

I tried to explain this the best way I though possible. I do hope for some
direction or if this is even possible. Thanks so much in advance!

Hi Lost,

If your data are on Sheet1 and you're summarising them on another sheet,
where A1 contains the Category type, you could use a formula like:
=SUMIF(Sheet1!C:C,Sheet2!A1,Sheet1!D:D)
to add up all the values in column D on Sheet1 for which the category in
Column C match the type in cell A1 on your summary sheet. If you need to
summarise from both sets of columns on your data sheet, you could use:
=SUMIF(Sheet1!C:C,Sheet2!A1,Sheet1!D:D)+SUMIF(Shee t1!E:E,Sheet2!A1,Sheet1!F:
F)

Cheers

--
macropod
[MVP - Microsoft Word]


"Lost in reconcillation"
wrote in message ...
Is there a formula or function that will add the number of hours (by row)
based on category. Meaning if I create another worksheet and had the
information put into these cells can I get it to add together the number
of
hours based on the category by row and give me a total number of hours by
category.

MONDAY TUESDAY
A2= Name B2= $$ C2= Cat D2= Hours E2= Cat F2= Hours
A3= C3= Cat D3= Hours E3= Cat F3= Hours
A4= Name B4= $$ C4= Cat D4= Hours E4= Cat F4= Hours
A5= C5= Cat D5= Hours E5= Cat F5= Hours
A6= Name B6= $$ C6= Cat D6= Hours E6= Cat F6= Hours
A7= C7= Cat D7= Hours E7= Cat F7=
Hours

The reason for the blank spaces is that generally 1-person does 2
different
categories per day and therefore they general have say 4 hours at cat1 and
4
hours at cat2 just as an example. In addition, in the cells where I have
indicated $$ there would be a dollar amount there like $15.00.

Lets say the other worksheet looked like this:

A1= CAT 1
A2= SUM OF ANY CAT1 HOURS FOR THIS ROW, AND FOR THE ROW DIRECTLY BELOW IT
(A3)
A3= B2 $$ * SUM OF CAT HOURS FROM ROW DIRECTLY ABOVE

This same scenario would need to go into the next columns for however many
categories (generally not more then 6) and it would need to accomplish the
same thing but for the different categories. I.E. CAT2 etc. Then be able
to
go for as many as 20 rows down, and then making a total row with total
hours
and dollars by category.

I tried to explain this the best way I though possible. I do hope for
some
direction or if this is even possible. Thanks so much in advance!

Hopefully I understood correctly what you're looking for. So formula for A2
in sheet2 would be:
=SUMIF(Sheet1!C2:D3,A$1,Sheet1!E2:F3)

And formula for A3 in sheet2 would be:
=Sheet1!B2*A2
Copy both cells at the same time and paste down from A3 to A20.

Formula in A21(for total hours):
=SUM(IF(MOD(ROW(A1:A20),2)=1,A1:A20,0))
Note: this is an array formula, instead of just hitting enter after typing
the formula, you need to do Ctrl-Shift-Enter (hold Ctrl and Shift key, and
hit Enter key), if you do it correctly the formula will then look like
{=SUM(IF(MOD(ROW(A1:A20),2)=1,A1:A20,0))}

Formula in A22(for total dollars):
=SUM(IF(MOD(ROW(A1:A20),2)=0,A1:A20,0))
Again, Ctrl-Shift-Enter needed for this.

Finally you can copy the formulas to the columns to the right. Of course I
could be interpreting you request incorrectly and all this would be wrong. :)

-Simon

"Lost in reconcillation" wrote:

Is there a formula or function that will add the number of hours (by row)
based on category. Meaning if I create another worksheet and had the
information put into these cells can I get it to add together the number of
hours based on the category by row and give me a total number of hours by
category.

MONDAY TUESDAY
A2= Name B2= $$ C2= Cat D2= Hours E2= Cat F2= Hours
A3= C3= Cat D3= Hours E3= Cat F3= Hours
A4= Name B4= $$ C4= Cat D4= Hours E4= Cat F4= Hours
A5= C5= Cat D5= Hours E5= Cat F5= Hours
A6= Name B6= $$ C6= Cat D6= Hours E6= Cat F6= Hours
A7= C7= Cat D7= Hours E7= Cat F7= Hours

The reason for the blank spaces is that generally 1-person does 2 different
categories per day and therefore they general have say 4 hours at cat1 and 4
hours at cat2 just as an example. In addition, in the cells where I have
indicated $$ there would be a dollar amount there like $15.00.

Lets say the other worksheet looked like this:

A1= CAT 1
A2= SUM OF ANY CAT1 HOURS FOR THIS ROW, AND FOR THE ROW DIRECTLY BELOW IT
(A3)
A3= B2 $$ * SUM OF CAT HOURS FROM ROW DIRECTLY ABOVE

This same scenario would need to go into the next columns for however many
categories (generally not more then 6) and it would need to accomplish the
same thing but for the different categories. I.E. CAT2 etc. Then be able to
go for as many as 20 rows down, and then making a total row with total hours
and dollars by category.

I tried to explain this the best way I though possible. I do hope for some
direction or if this is even possible. Thanks so much in advance!

Thank you for the input. I see I didn't mention that the category is not
always the same each day it can be several different number (between 1-16)
So yes I am summarizing but i'm trying to get it to extract the categories by
row by category to get a total number of hours/dollars for each category.
But because any individual could have a multitude of variations in category
for the week I was not able to figure any (if one exists) formula that would
do this for me based on only inputting category and hours by day. That is
basically what i'm looking for.

"SiC" wrote:

Hopefully I understood correctly what you're looking for. So formula for A2
in sheet2 would be:
=SUMIF(Sheet1!C2:D3,A$1,Sheet1!E2:F3)

And formula for A3 in sheet2 would be:
=Sheet1!B2*A2
Copy both cells at the same time and paste down from A3 to A20.

Formula in A21(for total hours):
=SUM(IF(MOD(ROW(A1:A20),2)=1,A1:A20,0))
Note: this is an array formula, instead of just hitting enter after typing
the formula, you need to do Ctrl-Shift-Enter (hold Ctrl and Shift key, and
hit Enter key), if you do it correctly the formula will then look like
{=SUM(IF(MOD(ROW(A1:A20),2)=1,A1:A20,0))}

Formula in A22(for total dollars):
=SUM(IF(MOD(ROW(A1:A20),2)=0,A1:A20,0))
Again, Ctrl-Shift-Enter needed for this.

Finally you can copy the formulas to the columns to the right. Of course I
could be interpreting you request incorrectly and all this would be wrong. :)

-Simon

"Lost in reconcillation" wrote:

Is there a formula or function that will add the number of hours (by row)
based on category. Meaning if I create another worksheet and had the
information put into these cells can I get it to add together the number of
hours based on the category by row and give me a total number of hours by
category.

MONDAY TUESDAY
A2= Name B2= $$ C2= Cat D2= Hours E2= Cat F2= Hours
A3= C3= Cat D3= Hours E3= Cat F3= Hours
A4= Name B4= $$ C4= Cat D4= Hours E4= Cat F4= Hours
A5= C5= Cat D5= Hours E5= Cat F5= Hours
A6= Name B6= $$ C6= Cat D6= Hours E6= Cat F6= Hours
A7= C7= Cat D7= Hours E7= Cat F7= Hours

The reason for the blank spaces is that generally 1-person does 2 different
categories per day and therefore they general have say 4 hours at cat1 and 4
hours at cat2 just as an example. In addition, in the cells where I have
indicated $$ there would be a dollar amount there like $15.00.

Lets say the other worksheet looked like this:

A1= CAT 1
A2= SUM OF ANY CAT1 HOURS FOR THIS ROW, AND FOR THE ROW DIRECTLY BELOW IT
(A3)
A3= B2 $$ * SUM OF CAT HOURS FROM ROW DIRECTLY ABOVE

This same scenario would need to go into the next columns for however many
categories (generally not more then 6) and it would need to accomplish the
same thing but for the different categories. I.E. CAT2 etc. Then be able to
go for as many as 20 rows down, and then making a total row with total hours
and dollars by category.

I tried to explain this the best way I though possible. I do hope for some
direction or if this is even possible. Thanks so much in advance!

Still looking for help/suggestions to this question. Thanks :)

"Lost in reconcillation" wrote:

Thank you for the input. I see I didn't mention that the category is not
always the same each day it can be several different number (between 1-16)
So yes I am summarizing but i'm trying to get it to extract the categories by
row by category to get a total number of hours/dollars for each category.
But because any individual could have a multitude of variations in category
for the week I was not able to figure any (if one exists) formula that would
do this for me based on only inputting category and hours by day. That is
basically what i'm looking for.

"SiC" wrote:

Hopefully I understood correctly what you're looking for. So formula for A2
in sheet2 would be:
=SUMIF(Sheet1!C2:D3,A$1,Sheet1!E2:F3)

And formula for A3 in sheet2 would be:
=Sheet1!B2*A2
Copy both cells at the same time and paste down from A3 to A20.

Formula in A21(for total hours):
=SUM(IF(MOD(ROW(A1:A20),2)=1,A1:A20,0))
Note: this is an array formula, instead of just hitting enter after typing
the formula, you need to do Ctrl-Shift-Enter (hold Ctrl and Shift key, and
hit Enter key), if you do it correctly the formula will then look like
{=SUM(IF(MOD(ROW(A1:A20),2)=1,A1:A20,0))}

Formula in A22(for total dollars):
=SUM(IF(MOD(ROW(A1:A20),2)=0,A1:A20,0))
Again, Ctrl-Shift-Enter needed for this.

Finally you can copy the formulas to the columns to the right. Of course I
could be interpreting you request incorrectly and all this would be wrong. :)

-Simon

"Lost in reconcillation" wrote:

Is there a formula or function that will add the number of hours (by row)
based on category. Meaning if I create another worksheet and had the
information put into these cells can I get it to add together the number of
hours based on the category by row and give me a total number of hours by
category.

MONDAY TUESDAY
A2= Name B2= $$ C2= Cat D2= Hours E2= Cat F2= Hours
A3= C3= Cat D3= Hours E3= Cat F3= Hours
A4= Name B4= $$ C4= Cat D4= Hours E4= Cat F4= Hours
A5= C5= Cat D5= Hours E5= Cat F5= Hours
A6= Name B6= $$ C6= Cat D6= Hours E6= Cat F6= Hours
A7= C7= Cat D7= Hours E7= Cat F7= Hours

The reason for the blank spaces is that generally 1-person does 2 different
categories per day and therefore they general have say 4 hours at cat1 and 4
hours at cat2 just as an example. In addition, in the cells where I have
indicated $$ there would be a dollar amount there like $15.00.

Lets say the other worksheet looked like this:

A1= CAT 1
A2= SUM OF ANY CAT1 HOURS FOR THIS ROW, AND FOR THE ROW DIRECTLY BELOW IT
(A3)
A3= B2 $$ * SUM OF CAT HOURS FROM ROW DIRECTLY ABOVE

This same scenario would need to go into the next columns for however many
categories (generally not more then 6) and it would need to accomplish the
same thing but for the different categories. I.E. CAT2 etc. Then be able to
go for as many as 20 rows down, and then making a total row with total hours
and dollars by category.

I tried to explain this the best way I though possible. I do hope for some
direction or if this is even possible. Thanks so much in advance!

Thank you macropod. With some minor adjustments to the worksheet I was able
to use your suggestions and get the end result i was looking for. Thanks!

"macropod" wrote:

Hi Lost,

If your data are on Sheet1 and you're summarising them on another sheet,
where A1 contains the Category type, you could use a formula like:
=SUMIF(Sheet1!C:C,Sheet2!A1,Sheet1!D:D)
to add up all the values in column D on Sheet1 for which the category in
Column C match the type in cell A1 on your summary sheet. If you need to
summarise from both sets of columns on your data sheet, you could use:
=SUMIF(Sheet1!C:C,Sheet2!A1,Sheet1!D:D)+SUMIF(Shee t1!E:E,Sheet2!A1,Sheet1!F:
F)

Cheers

--
macropod
[MVP - Microsoft Word]


"Lost in reconcillation"
wrote in message ...
Is there a formula or function that will add the number of hours (by row)
based on category. Meaning if I create another worksheet and had the
information put into these cells can I get it to add together the number
of
hours based on the category by row and give me a total number of hours by
category.

MONDAY TUESDAY
A2= Name B2= $$ C2= Cat D2= Hours E2= Cat F2= Hours
A3= C3= Cat D3= Hours E3= Cat F3= Hours
A4= Name B4= $$ C4= Cat D4= Hours E4= Cat F4= Hours
A5= C5= Cat D5= Hours E5= Cat F5= Hours
A6= Name B6= $$ C6= Cat D6= Hours E6= Cat F6= Hours
A7= C7= Cat D7= Hours E7= Cat F7=
Hours

The reason for the blank spaces is that generally 1-person does 2
different
categories per day and therefore they general have say 4 hours at cat1 and
4
hours at cat2 just as an example. In addition, in the cells where I have
indicated $$ there would be a dollar amount there like $15.00.

Lets say the other worksheet looked like this:

A1= CAT 1
A2= SUM OF ANY CAT1 HOURS FOR THIS ROW, AND FOR THE ROW DIRECTLY BELOW IT
(A3)
A3= B2 $$ * SUM OF CAT HOURS FROM ROW DIRECTLY ABOVE

This same scenario would need to go into the next columns for however many
categories (generally not more then 6) and it would need to accomplish the
same thing but for the different categories. I.E. CAT2 etc. Then be able
to
go for as many as 20 rows down, and then making a total row with total
hours
and dollars by category.

I tried to explain this the best way I though possible. I do hope for
some
direction or if this is even possible. Thanks so much in advance!