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

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

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

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" wrote in message
...
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

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

Hello Bernie,

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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

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

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

"The Fool on the Hill" wrote:

Hello Bernie,

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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

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

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

"dlw" wrote:

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

"The Fool on the Hill" wrote:

Hello Bernie,

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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

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

Fool,

I think the pivot table is the best option.

HTH,
Bernie
MS Excel MVP


"The Fool on the Hill" wrote in message
...
Hello Bernie,

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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

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

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" wrote:

Fool,

I think the pivot table is the best option.

HTH,
Bernie
MS Excel MVP


"The Fool on the Hill" wrote in message
...
Hello Bernie,

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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

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

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

HTH,
Bernie
MS Excel MVP


"The Fool on the Hill" wrote in message
...
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" wrote:

Fool,

I think the pivot table is the best option.

HTH,
Bernie
MS Excel MVP


"The Fool on the Hill" wrote in message
...
Hello Bernie,

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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