Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproducct question
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproducct question
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproducct question
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproducct question
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
UDF question | Excel Worksheet Functions | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
IF Question | Excel Worksheet Functions |