Sumproducct question

Can anyone help with a sumproduct question?

I would like to sum the values in range $N$16:$N$200 if the value in column
E of the row is not a capital letter and column M of the row is blank.

Column E can be blank, one uppercase letter, or lower case letter.

Thanks a bunch
John

There is probably a better awnser, but if it were only a few letters,
possible you could probalby use CHAR(x) in your IF formula. CHAR(77) =
capital M, whild CHAR(109), returns a small m. Also not sure if it can
compare that in a formula to what is in colm E.
--Decimal

"DocBrown" wrote:

Can anyone help with a sumproduct question?

I would like to sum the values in range $N$16:$N$200 if the value in column
E of the row is not a capital letter and column M of the row is blank.

Column E can be blank, one uppercase letter, or lower case letter.

Thanks a bunch
John

Here is one solution (I am sure there will be a more elegant solution)...
Type or paste the following in a cell and then press CTRL-SHIFT-ENTER
=SUMPRODUCT(IF(E16:E200"",IF(CODE(E16:E200)COD E(UPPER(E16:E200)),N16:N200,0),0),--(M16:M200=""))

It will add up column N where Col E has a lower case letter and Col M is blank

"DocBrown" wrote:

Can anyone help with a sumproduct question?

I would like to sum the values in range $N$16:$N$200 if the value in column
E of the row is not a capital letter and column M of the row is blank.

Column E can be blank, one uppercase letter, or lower case letter.

Thanks a bunch
John

How about a nice macro.

Sub sumifucase()
For i = 16 To 200
If Len(Application.Trim(Cells(i, "e"))) 0 _
And StrComp(UCase(Cells(i, "e")), Cells(i, "e")) 0 _
And Len(Application.Trim(Cells(i, "M"))) = 0 _
Then ms = ms + Cells(i, "n")
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DocBrown" wrote in message
...
Can anyone help with a sumproduct question?

I would like to sum the values in range $N$16:$N$200 if the value in
column
E of the row is not a capital letter and column M of the row is blank.

Column E can be blank, one uppercase letter, or lower case letter.

Thanks a bunch
John

=SUM(IF(rngE"",IF((CODE(rngE)=97)*(CODE(rngE=1 22))*(rngM=""),rngN)))


"DocBrown" wrote:

Can anyone help with a sumproduct question?

I would like to sum the values in range $N$16:$N$200 if the value in column
E of the row is not a capital letter and column M of the row is blank.

Column E can be blank, one uppercase letter, or lower case letter.

Thanks a bunch
John