Sumproduct keeps giving zero
 

I am using Excel 2002 and have a set of data that I am working with,
trying, unsuccessfully, to calculate an over or under availability of
resource.

The data is in rows 2 thru 986 and the summaries are in 987 onwards

Column I contains the team name
Column K contains the type of entry (Project, Base/BAU, Available
Project, Available Remander)
Column P contains the effort allocated/available

I want to sum the Project and Base/BAU figures and subtract the sum of
Available Project/Available Remainder

My formula is as follows, however the result is always 0.
{=(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Project"),--
($K2:$K986="Base/BAU"),P$2:P$986))-(SUMPRODUCT(--($I2:$I986=$I987),--
($K2:$K986="Available Project"),--($K2:$K986="Available Remainder"),P
$2:P$986))}

Any help gratefully received
Fred

Sumproduct keeps giving zero
 

Hi Fred

You can not test for both Project and Base/BAU in one SumProduct formula. If
the one return true, the other will return false, and then this row will
return a 0.

The first part of your formula should be:

=(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Project"),P$2:P$986)+(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Base/BAU"),P$2:P$986))

Regards,
Per

"Fred" skrev i meddelelsen
...
I am using Excel 2002 and have a set of data that I am working with,
trying, unsuccessfully, to calculate an over or under availability of
resource.

The data is in rows 2 thru 986 and the summaries are in 987 onwards

Column I contains the team name
Column K contains the type of entry (Project, Base/BAU, Available
Project, Available Remander)
Column P contains the effort allocated/available

I want to sum the Project and Base/BAU figures and subtract the sum of
Available Project/Available Remainder

My formula is as follows, however the result is always 0.
{=(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Project"),--
($K2:$K986="Base/BAU"),P$2:P$986))-(SUMPRODUCT(--($I2:$I986=$I987),--
($K2:$K986="Available Project"),--($K2:$K986="Available Remainder"),P
$2:P$986))}

Any help gratefully received
Fred

Sumproduct keeps giving zero
 

Hi Per,

Thanks for that, so obvious, but it takes someone else to point out
the obvious.

Regards
Fred

Sumproduct keeps giving zero
 

=(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Project"),P$2:P$986)+(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Base/BAU"),P$2:P$986))

Another way:

=(SUMPRODUCT(--($I2:$I986=$I987),--(ISNUMBER(MATCH($K2:$K986,{"Project","Base/BAU"},0))),P$2:P$986))

--
Biff
Microsoft Excel MVP


"Per Jessen" wrote in message
...
Hi Fred

You can not test for both Project and Base/BAU in one SumProduct formula.
If the one return true, the other will return false, and then this row
will return a 0.

The first part of your formula should be:

=(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Project"),P$2:P$986)+(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Base/BAU"),P$2:P$986))

Regards,
Per

"Fred" skrev i meddelelsen
...
I am using Excel 2002 and have a set of data that I am working with,
trying, unsuccessfully, to calculate an over or under availability of
resource.

The data is in rows 2 thru 986 and the summaries are in 987 onwards

Column I contains the team name
Column K contains the type of entry (Project, Base/BAU, Available
Project, Available Remander)
Column P contains the effort allocated/available

I want to sum the Project and Base/BAU figures and subtract the sum of
Available Project/Available Remainder

My formula is as follows, however the result is always 0.
{=(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Project"),--
($K2:$K986="Base/BAU"),P$2:P$986))-(SUMPRODUCT(--($I2:$I986=$I987),--
($K2:$K986="Available Project"),--($K2:$K986="Available Remainder"),P
$2:P$986))}

Any help gratefully received
Fred