PDA

View Full Version : [SOLVED] Sum Lookup



lui_roc
03-04-2006, 11:31 AM
I would like a formula to total the risk impact columns (cost, time, safety, quality and impact quotient), which are only negative values, based on the likelihood.

In the likelihood column value:
1 = Rare
2 = Unlikely
3 = Possible
4 = Likely
5 = Almost Certain

For example, the formula for cell G22 on the project register sheet will sum every negative cost value (column G) that has a likelihood (column F) of 1.

I hope that makes sense

My thanks in advance
Lui
Attached Fileshttp://www.vbaexpress.com/forum/images/attach/xls.gifRiskRegister_V18.xls (http://vbaexpress.com/forum/attachment.php?attachmentid=2868&d=1141495057) (66.0 KB, 0 views)

malik641
03-04-2006, 11:43 AM
I think you are looking for this...but not too sure:
Type this into G22

=COUNTIF(F7:F16,1)*-1

Yes, no? :dunno

EDIT: Sorry, I'm a dummy....didn't read your request carefully enough. Give me a minute.

lui_roc
03-04-2006, 11:52 AM
No - I'm not explaining it very well, i'll try again

I want to enter a formula in G22 that will sum only the negative numbers in column G that have a likleihood of 1, column F.

malik641
03-04-2006, 11:53 AM
No, you explained it fine...I just read it wrong.

Anyway:


=SUMPRODUCT((F7:F16=1)*(G7:G16<0)*(G7:G16))

Tell me if this is what you needed.

lui_roc
03-04-2006, 11:57 AM
Wow that was quick. Works perfect.
You've saved me loads of time

many thanks

Lui

malik641
03-04-2006, 11:58 AM
:thumb Nice, glad to help :yes