I want to calculate the number of actions closed and number of actions
pending as following. Column C I use Countif to solve. Column D and E???

Worksheet A is the summary of worksheet B
Column B Column C Column D Column E
(Name of Action needed) Total actions Actions closed Actions pending
Name A 3 ?
?
Name B 4 ?
?
Name C 3 ?
?
Name D 3 ?
?

Worksheet B
Column H Column K Column AC
(Actions Items) (Name for actions) (Action closed date)
Do A Name A 01 Jan 08
Do B Name A
Do C Name A 02 Jan 08
Do D Name B 01 Feb 08
..
..
..

For actions closed:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100<""))

For pending actions:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100=""))

Copy down as needed.


--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
I want to calculate the number of actions closed and number of actions
pending as following. Column C I use Countif to solve. Column D and E???

Worksheet A is the summary of worksheet B
Column B Column C Column D Column
E
(Name of Action needed) Total actions Actions closed Actions
pending
Name A 3 ?
?
Name B 4 ?
?
Name C 3 ?
?
Name D 3 ?
?

Worksheet B
Column H Column K Column AC
(Actions Items) (Name for actions) (Action closed date)
Do A Name A 01 Jan 08
Do B Name A
Do C Name A 02 Jan 08
Do D Name B 01 Feb 08
.
.
.

Result is #NUM! error.

"T. Valko" wrote:

For actions closed:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100<""))

For pending actions:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100=""))

Copy down as needed.


--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
I want to calculate the number of actions closed and number of actions
pending as following. Column C I use Countif to solve. Column D and E???

Worksheet A is the summary of worksheet B
Column B Column C Column D Column
E
(Name of Action needed) Total actions Actions closed Actions
pending
Name A 3 ?
?
Name B 4 ?
?
Name C 3 ?
?
Name D 3 ?
?

Worksheet B
Column H Column K Column AC
(Actions Items) (Name for actions) (Action closed date)
Do A Name A 01 Jan 08
Do B Name A
Do C Name A 02 Jan 08
Do D Name B 01 Feb 08
.
.
.

Result is #NUM! error.

You can't use entire columns as range references with SUMPRODUCT (unless
you're using Excel 2007).

Use a specific range.

--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
Result is #NUM! error.

"T. Valko" wrote:

For actions closed:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100<""))

For pending actions:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100=""))

Copy down as needed.


--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
I want to calculate the number of actions closed and number of actions
pending as following. Column C I use Countif to solve. Column D and
E???

Worksheet A is the summary of worksheet B
Column B Column C Column D
Column
E
(Name of Action needed) Total actions Actions closed Actions
pending
Name A 3 ?
?
Name B 4 ?
?
Name C 3 ?
?
Name D 3 ?
?

Worksheet B
Column H Column K Column AC
(Actions Items) (Name for actions) (Action closed date)
Do A Name A 01 Jan 08
Do B Name A
Do C Name A 02 Jan 08
Do D Name B 01 Feb 08
.
.
.

So thats the reason! It works now. Thanks.

"T. Valko" wrote:

Result is #NUM! error.

You can't use entire columns as range references with SUMPRODUCT (unless
you're using Excel 2007).

Use a specific range.

--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
Result is #NUM! error.

"T. Valko" wrote:

For actions closed:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100<""))

For pending actions:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100=""))

Copy down as needed.


--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
I want to calculate the number of actions closed and number of actions
pending as following. Column C I use Countif to solve. Column D and
E???

Worksheet A is the summary of worksheet B
Column B Column C Column D
Column
E
(Name of Action needed) Total actions Actions closed Actions
pending
Name A 3 ?
?
Name B 4 ?
?
Name C 3 ?
?
Name D 3 ?
?

Worksheet B
Column H Column K Column AC
(Actions Items) (Name for actions) (Action closed date)
Do A Name A 01 Jan 08
Do B Name A
Do C Name A 02 Jan 08
Do D Name B 01 Feb 08
.
.
.

You're welcome!

--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
So thats the reason! It works now. Thanks.

"T. Valko" wrote:

Result is #NUM! error.

You can't use entire columns as range references with SUMPRODUCT (unless
you're using Excel 2007).

Use a specific range.

--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
Result is #NUM! error.

"T. Valko" wrote:

For actions closed:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100<""))

For pending actions:

=SUMPRODUCT(--(Sheet2!K$2:K$100=B2),--(Sheet2!AC$2:AC$100=""))

Copy down as needed.


--
Biff
Microsoft Excel MVP


"Martin" wrote in message
...
I want to calculate the number of actions closed and number of
actions
pending as following. Column C I use Countif to solve. Column D and
E???

Worksheet A is the summary of worksheet B
Column B Column C Column D
Column
E
(Name of Action needed) Total actions Actions closed Actions
pending
Name A 3 ?
?
Name B 4 ?
?
Name C 3 ?
?
Name D 3 ?
?

Worksheet B
Column H Column K Column AC
(Actions Items) (Name for actions) (Action closed date)
Do A Name A 01 Jan 08
Do B Name A
Do C Name A 02 Jan 08
Do D Name B 01 Feb 08
.
.
.