# Thread: Month to Quarter Formula (Excel 2002/SP2)

1. ## Month to Quarter Formula (Excel 2002/SP2)

I'm trying to reformat some data that I am getting out of a reporting tool. The data current comes into Excel with products in column A and Months in columns B through Z. The month headers have a format of YYYYMM (i.e. 200309), but I really need to be able to view the data by quarters, halfs, and years. I plan to use the columns to the right to consolidate the data into quarters, halfs and years, but as I report on different date ranges in the reporting tool, the number of columns (i.e. months) change.

I realize that I could manually do a sum(B2:E2) for Q1 and so on, but I'd like to be able to use the month headers with maybe a sumif command so that it would be a single formula across the cells.

Any help would be appreciated.

2. ## Re: Month to Quarter Formula (Excel 2002/SP2)

If your 200209 is in row 1, you could add 2 rows ( one for "qtr", one for "half")
In B2 for example enter the formula to cal qtr:
=INT(VALUE(RIGHT(B1,2)-1)/3)+1
and in B3 for the half:
=INT(VALUE(RIGHT(B1,2)-1)/6)+1
Copy B2:B3 to c2:Z3

Then use sumifs with those 2 new rows
Steve

3. ## Re: Month to Quarter Formula (Excel 2002/SP2)

Steve,

That works for breaking down the headers into quarters, halfs (and years), but I now need to do a mulptiple "sumif". How can I say "If the years match and the quarters match, sum the cells"?

Thanks

4. ## Re: Month to Quarter Formula (Excel 2002/SP2)

You could use a SUMPRODUCT formula (but I would need some details about how you decided to set it up) or even an array formula, but it might just be easier to add the year to the qtr ( and half) formulas:

=left(B1,4)&"-"INT(VALUE(RIGHT(B1,2)-1)/3)+1
=left(B1,4)&"-"INT(VALUE(RIGHT(B1,2)-1)/6)+1

and then do the conventional sumif

Steve

5. ## Re: Month to Quarter Formula (Excel 2002/SP2)

Steve,

Thanks for the help. It works. It's not quite as elegant as I had hoped, but it works.

6. ## Re: Month to Quarter Formula (Excel 2002/SP2)

I used to work in manufacturing: I am happy with something working - elegance is for the academics.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•