Sumproduct keeps giving zero

[Fred]

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$986))-(SUMPRODUCT(--($I2:$I986=$I987),--
($K2:$K986="Available Project"),--($K2:$K986="Available Remainder"),P
$2$986))}

Any help gratefully received
Fred

 

[Per Jessen]

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$986)+(SUMPRODUCT(--($I2:$I986=$I987),--($K2:$K986="Base/BAU"),P$2$986))

Regards,
Per

 

[Fred]

Hi Per,

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

Regards
Fred

 

[T. Valko]

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

Another way:

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