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

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

Hi Per,

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

Regards
Fred

=(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