A #Value error in Sumproduct is caused by having ranges that aren't the same
size. In your formula, the problem is the range i8:j8. It contains only 2
cells, your other ranges are 6 sells. Once you have consistent ranges, that
should get rid of the #Value error.

Regards,
Fred

"IanH" wrote in message
...
Hi Fred,

I have used the multiplication rather than addition but I am still
experiencing the #VALUE! error msg. I assume that the data look up is
failing
somewhere and displaying the error message, rather than a formula error.

I am not actually sure that this formula will return the value that I am
looking for?

In this calculation I am hoping to display a numeric value of the number
of
all OPEN Risks that are counted for the month (the value displayed in Cell
F2
i.e. Mar-2010) where their corresponding Severity & Probability is equal
to H
meaning High.

My formula is now using ,--('Risk Log'!Q8:Q13="OPEN"),--('Risk
Log'!$I$8:$J$8="H")) values.

I cannot see where the COUNT is executed and it looks like the Cells I8 to
J8 must ALL have a value of H to be TRUE and therefore counted where the
requirement is for each ROW that has an OPEN status (and opened in the
month
specified by Cell F2) and has its ROW corresponding Probability &
Reliability
Cells to have a H value in them both - to be counted.

Can you assist further with this? Thanks in advance


"Fred Smith" wrote:

Your addition should be multiplication. But for consistency, I would use:
=SUMPRODUCT(--(MONTH('Risk Log'!$L$8:$L$13)=MONTH('Risk Performance
Metrics'!F$2)),--(YEAR('Risk Log'!$L$8:$L$13)=YEAR('Risk Performance
Metrics'!F$2)),--('Risk Log'!Q8:Q13="OPEN"),--('Risk Log'!$I$8:$J$8="H"))

You can also simplify the date check as Don has shown.

Regards,
Fred

"IanH" wrote in message
...
Fred,

That worked a treat, thank you very much.

As part of the spreadsheet calculation I am also experiencing errors in
the
following calculation:

=SUMPRODUCT(--(MONTH('Risk Log'!$L$8:$L$13)=MONTH('Risk Performance
Metrics'!F$2)),--(YEAR('Risk Log'!$L$8:$L$13)=YEAR('Risk Performance
Metrics'!F$2))+('Risk Log'!Q8:Q13="OPEN")+('Risk Log'!$I$8:$J$8="H"))

I am trying to retrieve the number of RISKS that are OPEN during the
same
DATE cell as F2 (i.e. Mar-2010) that have a CELL value of H (HIGH) in
both
Severity Column (values run from I8 to I13 cells) and Probability
Column
(values run from J8 to J13 cells) but I am getting the #VALUE! error
message??

Im laymans terms, I am trying to process the logic as:
-- List all OPEN Risks for Mar-2010 (or whatever month I list in F2)
where
the Risk has an OPEN status AND a Severity and Probability value of H.

Can you steer me down the right track? Thanks

"IanH" wrote:

I have been trying to work out how to go about calculating multiple
conditionals using the Wizard. In MS 2003 you can use the Add
button.......in
2007 it seems to have disappeared???
I was hoping to be able to calculate the following for a Risk
spreadsheet.
For each RED, AMBER, GREEN (RAG) cell (manually filled with
appropriate
colour), I want to automatically work out the RAG status and fill with
colour
(or use traffic ligts) based on the following.

Example:
the RAG cell is B8 (I wish to calculate this automatically)
I have a Severity cell I8 and Probability Cell J8
There are 3 levels, H (HIGH), M (MEDIUM), L (LOW). I manually populate
these
values.

The conditions a
If I8 is equal to H and J8 is equal to H then B8 should have a fill of
RED
If I8 is equal to H and J8 is equal to M then B8 should have a fill of
RED
If I8 is equal to M and J8 is equal to H then B8 should have a fill of
RED

If I8 is equal to M and J8 is equal to M then B8 should have a fill of
AMBER
If I8 is equal to M and J8 is equal to L then B8 should have a fill of
AMBER
If I8 is equal to L and J8 is equal to M then B8 should have a fill of
GREEN

If I8 is equal to L and J8 is equal to L then B8 should have a fill of
GREEN
If I8 is equal to L and J8 is equal to M then B8 should have a fill of
GREEN
If I8 is equal to M and J8 is equal to L then B8 should have a fill of
GREEN

Is there a simple way to do this? Thanks in advance

.