[bhav]

I want to do a union of two columns in Excel by using some function. for eg one column has nos like 0,1,2 and the other has fields passed and failed.I want to count the no of 0s along with passed and 0s with failed and the rest.I have tried the and function but in vail.Please do tell me how to do the union of the two columns in Excel itself.
Thanks

[Advertisements]

The first part of your question is how to combine columns.
If Cell A1 = "0", B1 = "Passed", C1 = "=A1&B1"
Then C1 will display "0Passed"

By copying the formula in C1 down you can combine the columns.


The second part of your question referred to counting the results.
You do not need to combine the columns to do this.
A much more effective way is to use a pivot table.

The result of a pivot table will look something like

NUM 0 1 2
STATUS
Passed 4 22 9
Failed 11 8 16

The following link has some more info on pivot tables.
http://www.exceltip....Report/283.html



I have also attached a sample file.
If you right click anywhere in the pivot table results and select wizard you will be able to step back and forth through the Pivot options.



Regards
David

Attached Files

•  SimplePivot.zip   3.53KB   348 downloads

[dsm]

The first part of your question is how to combine columns.
If Cell A1 = "0", B1 = "Passed", C1 = "=A1&B1"
Then C1 will display "0Passed"

By copying the formula in C1 down you can combine the columns.


The second part of your question referred to counting the results.
You do not need to combine the columns to do this.
A much more effective way is to use a pivot table.

The result of a pivot table will look something like

NUM 0 1 2
STATUS
Passed 4 22 9
Failed 11 8 16

The following link has some more info on pivot tables.
http://www.exceltip....Report/283.html



I have also attached a sample file.
If you right click anywhere in the pivot table results and select wizard you will be able to step back and forth through the Pivot options.



Regards
David

Attached Files

•  SimplePivot.zip   3.53KB   348 downloads

[jasonharrod]

I'm not really sure of what you are trying to do, here is what I came up with


just do a Append on the columns you need and then do a countif(range,"Criteria")
then do a Counta(range)-the 0failed countif(range,criteria) - 0Passed countif(range,"Criteria") to get everything else.


Hope that helped

Edited by jasonharrod, 13 July 2005 - 09:20 AM.