Skip to Content

Cross Tab Format Report

Hi,

I have data like this in xcelsius data sheet,

A B C

1 State Month Sales

2 Austin Jan 200

3 Austin Feb 300

4 Austin Mar 400

5 Texas Jan 400

6 Texas Feb 200

7 Texas Mar 500

8 DC JAn 100

9 DC Feb 300

10 DC Mar 150

I want to aggregate the sales values in the following format,

H I J K

1 State Jan Feb Mar

2 Austin 200 300 400

3 Texas 400 200 500

4 DC 100 300 150

I used SUMIFS function, unfortunately Xcelsius does not support this function.

Is there any work around to get the values in desired format using Excel functions.

Sorry in the preview the format is not shown properly, but iam looking for something like crosstab report format.

Regards

Sudhakar

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 06, 2011 at 09:44 PM

    You can pivot table to get the aggregation.

    You can have state in rows and months in columns.

    Hope this helps your problem.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 13, 2011 at 12:18 PM

    Hi Sudhakar,

    One way to do this is to create a new column say column D which contains a concatenation of columns A and B e.g.

    	A	B	C	D
    1	State	Month	Sales	Combined
    2	Austin	Jan	200	AustinJan
    3	Austin	Feb	300	AustinFeb
    4	Austin	Mar	400	AustinMar
    5	Texas	Jan	400	TexasJan
    6	Texas	Feb	200	TexasFeb
    7	Texas	Mar	500	TexasMar
    8	DC	JAn	100	DCJAn
    9	DC	Feb	300	DCFeb
    10	DC	Mar	150	DCMar

    The use this formula in cell I2 which can then be copied down and accross as required =SUMIF($D$2:$D$10,$H2&I$1,$C$2:$C$10)

    That is where the setup is as you described above i.e.

    	H	I	J	K
    1	State	Jan	Feb	Mar
    2	Austin	200	300	400
    3	Texas	400	200	500
    4	DC	100	300	150

    Regards,

    Paul

    Edited by: zygorf on Dec 13, 2011 12:19 PM

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.