How to do a Cross-Tab in Excel 2007?

**UrbanEast** · 10-31-2010, 08:15 PM

Is there any way to do these in Excel 2007 WITHOUT a Pivot Table? I'd like to teach my Stats AP students how to do these without having them learn Pivot tables. What I'd like is to take data that looks like this

Gender SAT
M 1300
F 1200
M 1400
F 1300
F 1500
M 1000

And to turn it into a two-way Frequency Table like this

<1200 > >=200
M _____% ____%
F _____% ____%

Is this possible without going through and counting the number of cells in each pair of categories manually? Thanks very much.

**teylyn** · 10-31-2010, 08:19 PM

Hello,

you can use Countifs for that, if you don't want to create a pivot table.

=COUNTIFS($A$2:$A$7,$E2,$B$2:$B$7,F$1)

This formula is in F2 of the attached file, copied down and across.

cheers,

**teylyn** · 10-31-2010, 08:21 PM

To express the result as a percentage, change the formula to

=COUNTIFS($A$2:$A$7,$E2,$B$2:$B$7,F$1)/COUNTA($A$2:$A$7)

and format the cell as %

cheers

**UrbanEast** · 10-31-2010, 10:46 PM

Originally Posted by teylyn

To express the result as a percentage, change the formula to

=COUNTIFS($A$2:$A$7,$E2,$B$2:$B$7,F$1)/COUNTA($A$2:$A$7)

and format the cell as %

cheers

Thanks very much!

**Timo1986** · 01-06-2012, 05:27 AM

Hi,

Could someone please tell me how you can get exactly the same results as displayed above in pivot table format (with the different criterias <1200 and >=1200 displayed in different columns)

I tried to display the results by using a pivot table, but I didn't succeed.

Thanks in advance..

Kind regards,
Timo

**teylyn** · 01-06-2012, 05:33 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.