| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct with more than 1 selection
Hi all,
I am using the following formular which works fine. The Z... are named ranges of columns. =SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt) But If I want to select a number of additional selections to Zinvest in the same column e.g. A, B, C I seem to be getting a problem. Tried this which resulted in Zero. =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt) Also tried this which resulted in #VALUE =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C")),Zamt) Any Suggestions? Many Thanks TW |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SumProduct with more than 1 selection
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SumProduct with more than 1 selection
=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest={"A","B","C"}),Zamt)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hi all, I am using the following formular which works fine. The Z... are named ranges of columns. =SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt) But If I want to select a number of additional selections to Zinvest in the same column e.g. A, B, C I seem to be getting a problem. Tried this which resulted in Zero. =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Z invest="C"),Zamt) Also tried this which resulted in #VALUE =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B") ,--(Zinvest="C")),Zamt) Any Suggestions? Many Thanks TW |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SumProduct with more than 1 selection
TW,
In SUMPRODUCT formulas, you can use + as an analog to OR: Your idea: =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt) Could also be =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),((Zinvest="A")+(Zinvest="B")+(Zinves t="C")),Zamt) Just an alternative to the array version, and one which I find easier to remember.... HTH, Bernie MS Excel MVP wrote in message ups.com... Hi all, I am using the following formular which works fine. The Z... are named ranges of columns. =SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt) But If I want to select a number of additional selections to Zinvest in the same column e.g. A, B, C I seem to be getting a problem. Tried this which resulted in Zero. =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt) Also tried this which resulted in #VALUE =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C")),Zamt) Any Suggestions? Many Thanks TW |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SumProduct with more than 1 selection
Hi all,
Thanks for the advise. Using the array method still came up with an error message #VALUE if I use more than one array. But works fine for invividual letters. So tried Bernie's method and... YES!! this works great. Thanks again TW Bernie Deitrick wrote: TW, In SUMPRODUCT formulas, you can use + as an analog to OR: Your idea: =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt) Could also be =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),((Zinvest="A")+(Zinvest="B")+(Zinves t="C")),Zamt) Just an alternative to the array version, and one which I find easier to remember.... HTH, Bernie MS Excel MVP wrote in message ups.com... Hi all, I am using the following formular which works fine. The Z... are named ranges of columns. =SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt) But If I want to select a number of additional selections to Zinvest in the same column e.g. A, B, C I seem to be getting a problem. Tried this which resulted in Zero. =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt) Also tried this which resulted in #VALUE =SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C")),Zamt) Any Suggestions? Many Thanks TW |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Limiting selection in a cell AND linking that selection to a list | Excel Discussion (Misc queries) | |||
| Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
| Storing selection & using the saved selection adress later | Excel Programming | |||
| Object Type of a selection... counting rows in a selection | Excel Programming | |||
| Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming | |||
Hybrid Mode
