Sumif / countif Problem

[Martin]

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

 

[T. Valko]

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.

 

[Martin]

Result is #NUM! error.

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.

 

[T. Valko]

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.

 

[Martin]

So thats the reason! It works now. Thanks.

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

Use a specific range.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

So thats the reason! It works now. Thanks.