Analyzing non numerical data in an excel spread sheet

[Guest]

Can i use excel to analyze non numerical data in a spread sheet?
I want to use functions to work out operator results using two or more
criteria based on daily results. is this possible?
colum one is date
collum 2 is ref word for his result which has 4 different variables
collum 3 is operater
but the operators initial will appear on more than one occasion on a set
date with different results each time.
what i want to do is look up the date, the operator and the number of times
each variable appears for that operator?
how is this possible

 

[Ken Wright]

Take a look at using a Pivot Table. It should default to the count function if
your data is non-numeric. Give us an example of your raw data and the type of
report you want to see and we can walk you through the steps on how to get
there.

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Guest]

2 ways to try ..

Assume you have
in Sheet1, cols A to C
data in row2 down

Date RefW Operator
01-Oct-04 Text1 XYZ
01-Oct-04 Text2 XYZ
01-Oct-04 Text2 XYZ
01-Oct-04 Text1 ABC

and you have set up the table below
in Sheet2

Date Operator Text1 Text2 Text3 Text4
01-Oct-04 XYZ
01-Oct-04 ABC

where the 4 variables (Text1, Text2, .. Text4) are listed in C1:F1
with the dates and operator initials listed down cols A and B, from row2

To populate the table:

Put in C2:

=SUMPRODUCT((Sheet1!$A$2:$A$5=$A2)*(Sheet1!$C$2:$C$5=$B2)*(Sheet1!$B$2:$B$5=C$1))

Copy C2 across to F2, then fill down

For the sample data in Sheet1, you'll get the results:

Date Operator Text1 Text2 Text3 Text4
01-Oct-04 XYZ 1 2 0 0
01-Oct-04 ABC 1 0 0 0

For a cleaner look in the results table,
you could suppress extraneous zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

----
Another way is to use the pivot-table report

Assuming the same data set-up in Sheet1 above

Click on any cell within the data-set in A1:C5

Click Data > Pivot Table Report
Click Next > Next

In step 3 of the wizard:

Drag and drop Date within the ROW area
Drag and drop Operator within the ROW area
(below Date)

Drag and drop RefW within the COLUMN area
Drag and drop RefW within the DATA area
(It'll show as Count of RefW)

Click Finish

The Pivot Table will appear on a new sheet to the left:

Count of RefW RefW
Date Operator Text1 Text2 Grand Total
01-Oct-04 ABC 1 1
XYZ 1 2 3
01-Oct-04 Total 2 2 4
Grand Total 2 2 4