formula problem!

[hhamlinwright08]

Hi all,

With reference to the example below, I have a database used for scoring vertebral deformities in fish (weird I know..).

I require a formula that will automatically recognise if the deformity region described in S2 and V2 is "Main Truncal", and if that is the case, to then SUM the # of vertebrae affected described in the adjacent cells T2 (3) and W2 (1). In this case the correct output that I am looking for is therefore 4.

Any ideas?!


<a href="http://tinypic.com?ref=2yubo5u" target="_blank"><img src="http://i63.tinypic.com/2yubo5u.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

 

[Jonmo1]

If say V2 was NOT 'Main Truncal', but S2 is, then you would want 3 ?
and 1 for Vice versa?

Try
=SUMIF(S2:V2,"Main Truncal",T2:W2)

 

[Rick Rothstein]

Give this formula a try...

=IF(AND(S2="Main Truncal",V2="Main Truncal"),T2+W2,"")

 

[hhamlinwright08]

Hi, thank you for the replies!

The problem I have is that some of the cells in the database are blank (see below).
The formula needs to be able to recognise and ignore blank cells, and sum the number of affected vertebrae if the correct deformity region is found in the adjacent cell.

I hope this makes sense..

<a href="http://tinypic.com?ref=2mmxugg" target="_blank"><img src="http://i65.tinypic.com/2mmxugg.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

 

[Rick Rothstein]

The problem I have is that some of the cells in the database are blank (see below).
The formula needs to be able to recognise and ignore blank cells, and sum the number of affected vertebrae if the correct deformity region is found in the adjacent cell.

That picture is hard to see, but I don't see how blank cells would affect the formula I posted. Can you give an example of values in Cells S2:T2 and V2:W2 that would cause the formula I posted to fail and then tell me what you want the formula to report for those values?

 

[Jonmo1]

but I don't see how blank cells would affect the formula I posted.

Or Mine.

 

[hhamlinwright08]

Thanks guys, this did the trick! - was inputting the formula incorrectly.
Another question, how could I adapt the formula below so that the number generated, e.g. '4' was then divided by 62?

Thanks!

=SUMIF(S187:AK187,"Main Caudal",T187:AL187)

 

[Jonmo1]

You should be able to just add /62 at the very end.