ExcelBanter - Sumif / countif Problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Sumif / countif Problem (https://www.excelbanter.com/excel-discussion-misc-queries/182480-sumif-countif-problem.html)

Martin

Sumif / countif Problem

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

Sumif / countif Problem

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

Martin

Sumif / countif Problem

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

T. Valko

Sumif / countif Problem

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

Martin

Sumif / countif Problem

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

T. Valko

Sumif / countif Problem

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

All times are GMT +1. The time now is 01:39 AM.