Complex Sumif
 

HELP! I need help with a complex sum formula. The data outlined below
contains over 1,000 rows and I need to build the matrix titled "Desired
Result" that summarizes forecasted task completions by quarter. There are 5
different engineering groups so the Engineering formula will need to look for
projects that start with "Engineer". There are 10 different projects in
column A and I have no problem with a separate formula for each project.

I hope the DATA and RESULT and self-explanatory. I could not copy them in
Excel format. For future reference, is an Excel copy possible?

DATA
Column A T U V W
Project Quarter Current Next Future
Engineering 2 4Q08 1
Engineering 1 3Q09 1
Engineering 2 2Q10 1
Engineering 3 3Q09 1
Design 3Q09 1
Design 1Q10 3

Desired Result
Quarter 4Q08 1Q09 2Q09 3Q09 4Q09 1Q10
Engineering
Current Year 1
Next Year 2
Future Years 1
Design
Current Year
Next Year
1
Future Years 3
--
JPS

Complex Sumif
 

Hi,

How are you? Your question is not very clear. Can you paste the data again
so that it is easy to understand the question. Alternatively, please feel
free to mail me the workbook at

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPS" wrote in message
...
HELP! I need help with a complex sum formula. The data outlined below
contains over 1,000 rows and I need to build the matrix titled "Desired
Result" that summarizes forecasted task completions by quarter. There are
5
different engineering groups so the Engineering formula will need to look
for
projects that start with "Engineer". There are 10 different projects in
column A and I have no problem with a separate formula for each project.

I hope the DATA and RESULT and self-explanatory. I could not copy them in
Excel format. For future reference, is an Excel copy possible?

DATA
Column A T U V W
Project Quarter Current Next Future
Engineering 2 4Q08 1
Engineering 1 3Q09 1
Engineering 2 2Q10 1
Engineering 3 3Q09 1
Design 3Q09 1
Design 1Q10 3

Desired Result
Quarter 4Q08 1Q09 2Q09 3Q09 4Q09 1Q10
Engineering
Current Year 1
Next Year 2
Future Years 1
Design
Current Year
Next Year
1
Future Years 3
--
JPS

Complex Sumif
 

I am confused as to why in the desired results you have this, next and
future year rows when the column headings include the year. So I would
expect Engineering to be just one row.

--
__________________________________
HTH

Bob

"JPS" wrote in message
...
HELP! I need help with a complex sum formula. The data outlined below
contains over 1,000 rows and I need to build the matrix titled "Desired
Result" that summarizes forecasted task completions by quarter. There are
5
different engineering groups so the Engineering formula will need to look
for
projects that start with "Engineer". There are 10 different projects in
column A and I have no problem with a separate formula for each project.

I hope the DATA and RESULT and self-explanatory. I could not copy them in
Excel format. For future reference, is an Excel copy possible?

DATA
Column A T U V W
Project Quarter Current Next Future
Engineering 2 4Q08 1
Engineering 1 3Q09 1
Engineering 2 2Q10 1
Engineering 3 3Q09 1
Design 3Q09 1
Design 1Q10 3

Desired Result
Quarter 4Q08 1Q09 2Q09 3Q09 4Q09 1Q10
Engineering
Current Year 1
Next Year 2
Future Years 1
Design
Current Year
Next Year
1
Future Years 3
--
JPS

Complex Sumif
 

Hi,

Try using Pivot tables.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPS" wrote in message
...
HELP! I need help with a complex sum formula. The data outlined below
contains over 1,000 rows and I need to build the matrix titled "Desired
Result" that summarizes forecasted task completions by quarter. There are
5
different engineering groups so the Engineering formula will need to look
for
projects that start with "Engineer". There are 10 different projects in
column A and I have no problem with a separate formula for each project.

I hope the DATA and RESULT and self-explanatory. I could not copy them in
Excel format. For future reference, is an Excel copy possible?

DATA
Column A T U V W
Project Quarter Current Next Future
Engineering 2 4Q08 1
Engineering 1 3Q09 1
Engineering 2 2Q10 1
Engineering 3 3Q09 1
Design 3Q09 1
Design 1Q10 3

Desired Result
Quarter 4Q08 1Q09 2Q09 3Q09 4Q09 1Q10
Engineering
Current Year 1
Next Year 2
Future Years 1
Design
Current Year
Next Year
1
Future Years 3
--
JPS