Checking status of issues based on first two digits of a field

  • Thread starter Thread starter The Fool on the Hill
  • Start date Start date
T

The Fool on the Hill

I want to know the number of issues with a certain status, of which the first
two digits of that issue starts with two digits. I have tried pivot table,
but that does not seem to work. Let me give an example:


Column A Column B
------------------------------------------------------
T2: CC-009 Open
T2: CC-002 New
T1: Issue on FAD Closed
T1: Berekening HASHTOTAL Open
T3: Incorrect text Closed

Now I am looking for a formula which gives me the following result:
Column X Column Y Column Z
(sort of issue) (Status) (Total)
---------------------------------------------
T1 Closed 1
T1 Open 1
T2 New 1
T2 Open 1
T3 Closed 1

Can you please help me
 
Fool,

Use a column of formulas

=LEFT(A2,2)

and use that column as your row field.

HTH,
Bernie
MS Excel MVP
 
What I want to know is the totals per status per sort of issue !!

SUMIF won't help in that area should be a sumproduct, I guess. But if I am
sure, then I wouldn't publish this issue.... ;o))
 
Hello Bernie,

Thank you very much, but when I use pivot, it will give me the entire
description, instead of just the two first digits, or can I influence that?
 
Use the column with the formula, not the column with the original data.

HTH,
Bernie
MS Excel MVP
 
Back
Top