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

[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

 

[Bernie Deitrick]

Fool,

Use a column of formulas

=LEFT(A2,2)

and use that column as your row field.

HTH,
Bernie
MS Excel MVP

 

[The Fool on the Hill]

Hello Bernie,

OK thank you but then I would need a sumproduct formula?

 

[dlw]

not exactly sure what you are trying to total, but probably SUMIF

 

[The Fool on the Hill]

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))

 

[Bernie Deitrick]

Fool,

I think the pivot table is the best option.

HTH,
Bernie
MS Excel MVP

 

[The Fool on the Hill]

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?

 

[Bernie Deitrick]

Use the column with the formula, not the column with the original data.

HTH,
Bernie
MS Excel MVP