Need to Sum Non Numerical Columns and Create a Chart in Office 2003 Excel/Power Point

[Mike]

Hi, I need to create a chart using Excel 2003 and or PowerPoint 2003. The
nonnumeric data is
stored in a multiple column spreadsheet. In one of the columns (A) the
cells are populated with letters (BT or PP or RD) I need a dynamic total of
the number of populated cells (not all cells are populated) in this column,
then I'd like to compare the total number of entries to another column (B)
that is populated w/ Xs, Rs and Ns. These letters refer to the status of
the PKI certificates i.e., received, requested or needed. So, I'd like
these values to be a dynamic chart that'll update every time a value is
added or changed. What I'm doing is rolling out PKI installations where
column A is location of the PKI installs and column B is status of PKI
certificates issued to users. I'm also collecting other user information
but is not relevant to this chart. Eventually, I'll convert the spreadsheet
to an access database.

Thanks,

Mike

 

[Mike]

Hi, I need to create a chart using Excel 2003 and or PowerPoint 2003. The
nonnumeric data is
stored in a multiple column spreadsheet. In one of the columns (A) the
cells are populated with letters (BT or PP or RD) I need a dynamic total
of the number of populated cells (not all cells are populated) in this
column, then I'd like to compare the total number of entries to another
column (B) that is populated w/ Xs, Rs and Ns. These letters refer to
the status of the PKI certificates i.e., received, requested or needed.
So, I'd like these values to be a dynamic chart that'll update every time
a value is added or changed. What I'm doing is rolling out PKI
installations where column A is location of the PKI installs and column B
is status of PKI certificates issued to users. I'm also collecting other
user information but is not relevant to this chart. Eventually, I'll
convert the spreadsheet to an access database.

Thanks,

Mike

Also, if possible, I'd like to subtotal the different values (BT PP RD) in
column A and X, R and N in column B.
Let me know if I haven't described the problem with enough detail.

 

[Bernard Liengme]

Not too clear but:
To count (you cannot 'sum' non-numeric items) how many cells contain text:
=COUNTA(A:A)
To count how many have a certain text: =COUNTIF(A:A,"BT")
To count how many times A col has BT and B col has X
=SUMPRODUCT(--(A1:A1000="BT"),--(B1:B1000="X")
You cannot use full column reference (A:A) with this function
For more on Sumproduct see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[Mike]

Not too clear but:
To count (you cannot 'sum' non-numeric items) how many cells contain text:
=COUNTA(A:A)
To count how many have a certain text: =COUNTIF(A:A,"BT")
To count how many times A col has BT and B col has X
=SUMPRODUCT(--(A1:A1000="BT"),--(B1:B1000="X")
You cannot use full column reference (A:A) with this function
For more on Sumproduct see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

OK, COUNTIF works. How can I combine the count of two COUNTIF expressions
(at least I think there arecalled expressions)

 

[Bernard Liengme]

Use SUMPRODUCT if there are two criteria
best wishes

 

[Mike]

Use SUMPRODUCT if there are two criteria
best wishes

Thanks, you've very helpful and your website is very helpful.

 

[lqlqlqqq]

To add non-numerical values you have two options:

Use function Concatenate or use &:

Enter: =(value1&value2&value3...)