Excel 2007: What tools do I use? 1

[ladyck3]

I've got a new project and I'm stumped. Data mining a survey.

Column V contains scores, 1, 2, 3, 4, 5.
Column X contains 1 or 0,
Column Y contain 2 or 0,
Column Z contains 3 or 0,
Column AA contains 4 or 0,
Column AB contains 5 or 0.

For now the 0 = No the number in the columns listed (X-AB)simply mean Yes...but I need to count in each X - AB, how many times Scores of 1 have a yes in column X, Y, Z....
How many times 2 has a yes in column X, y, Z....

A Vlookup does not allow use of a formula like a countif.... like an if statement does, I don't know if I should be using a nested IF statement... like I said, I'm needing help with the tools.

There is a MATCH and an INDEX function too but I'm not sure if those might apply.

I sure could use some guidance (and this may not be the last question on this project)

I need formulas, not VBA right now the intern that is doing this is manually counting/adding all data <shaking head>

I know Excel HAS to have a way.

Please advise, all suggestions are welcome!

LadyCK3
aka: Laurie

 

[SkipVought]

Hi,

Check out COUNTIF()

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

I know count if.. but how do I say.... if there are 24 "2"s in V, how many of those 2's have a "1" in column X?

LadyCK3
aka: Laurie

 

[GlennUK]

Use COUNTIFS function ... you can specify multiple conditions.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[combo]

Pivot table is an alternative.
Drag x, y, z, aa, ab as row fields, remove subtotals. Add calculation fields: count_x with formula =sum(x), count_y with formula =sum/2, count_z with formula =sum(z)/3, etc. Drag them as data fields, horizontal orientation.
As a result, you will get all existing combinations with nonzero items counted.

combo

 

[ladyck3]

GLENN!!!! OMG!!!! YOU ARE A GENIUS! YAYAYAYAY I was not aware of "countifs" it worked like a CHARM...

Sorry Combo... you lost me. However when I wrap my head around what you have said, that may help me down the line on this project.

Oh Glenn.... thanks SO MUCH PERFECTION!

Heck, I just didn't know "YET" was all

THANK YOU THANK YOU THANK YOU!!!

LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

Since you are using Excel 2007, the Data > Table feature is really cool. It has aggregations built in, with a single click in the Table Tools > Design tab (a context sensitive tab)

Check out Excel HELP
What's new in Microsoft Office Excel 2007 > Excel table enhancements and
[/b]Using structured references with Excel tables[/b] for starters


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Thank you Skip, I will take a look at that but honestly, I've tried to get a handle on this in the past and it kinda went WOOSH over my head (and they call me a guru) ARGH! I argue the point all the time.

I will devote some time to it, I promise.

LadyCK3
aka: Laurie

 

[combo]

To explain the pivot table application:
I assumed that the headers of a table are X, Y,...AB, table entries 0 or 1 in col. X, 0 or 2 in col. Y etc. Now the number of nonzero entries in each column is a sum of column divided by the value of its non-zero entry. This can be done in pivot table using calculation fields.
So the process:
1. create a pivot table with X, Y,..., AB as row fields (it may be necessary to add anything to data field to complete table creation),
2. add calculation fields with each column as argument, in the formula divide sum for column's non-zero entry,
3. customise table: remove data field if added in step 1,change data orientation if data fields are oriented vertically, remove messy subtotals.

As a result you will get a table where first five columns contain possible column values, the next five count non-zero values. The rows show all possible combinations of row entries in source data.

combo