PDA

View Full Version : Solved: Does sumproduct work with OR



mperrah
08-29-2007, 04:08 PM
I'm using this formula on the sheet for the source of a chart.
I need to add 4 regions that have multiple cities.
How could I make named range hold multiple criteria to be validated in the sumproduct.
I'm trying to avoid typing every case.


=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd))

For example, the region "Leonard" has 4 offices: Modesto, Sacramento, Susanville, and Carson.
In Modesto there are 3 management areas: 1X, 1Z, and 3L

I have a chart source that has a list of results from all the areas.

I need to pull a sumproduct for all 3 ma's when I scan the Office:

=sumproduct(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(MA=1X OR 1Z OR 3L))

or (ma=1z),--(ma=1X),--(ma=3L)
or named range "modesto" contents: 1Z OR 1X OR 3L
datavalidation list named MAList

(ma=MaList)


I need to total all three MA values:
example values of:1z=3 1x=4 3l=3
ma=10


any suggestions

Mark

Shazam
08-29-2007, 04:43 PM
=sumproduct(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(MA=1X OR 1Z OR 3L))
Mark

Do you mean this?

=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(MA={"1X","1Z","3L"}))


Hope it helps!

mperrah
08-29-2007, 05:16 PM
Shazam
I get a value error with this method
I tried with and w/o cse (ctrl+shft+enter)

btw
I grew up watching the TV show Shazam, any connection?
Mark

Shazam
08-29-2007, 05:34 PM
Shazam
I get a value error with this method
I tried with and w/o cse (ctrl+shft+enter)

It should be just enter. Can you post a sample of your workbook?


btw
I grew up watching the TV show Shazam, any connection?
Mark

I wasn't born yet but I'm a huge Marvel Family fan. One of my favorite characters is black Adam and Captain Marvel.

RichardSchollar
08-29-2007, 11:28 PM
Shazam
I get a value error with this method
I tried with and w/o cse (ctrl+shft+enter)

btw
I grew up watching the TV show Shazam, any connection?
Mark
Hi Mark

Are you sure that all your named ranges are the same size?

Richard

EDIT: They aren't the same size as the result of the last argument produces an array 3 'columns' wide - so, as Bob shows, you need to use the multiplication operator *. Or perhaps:

=sumproduct(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--ISNUMBER(MATCH(MA,{"1X","1Z","3L"},0)))

Bob Phillips
08-29-2007, 11:33 PM
=SUMPRODUCT((item01=scan_item)*(QCDate>=chStart)*(QCDate<=chEnd)*(MA={"1X","1Z","3L"}))

Bob Phillips
08-29-2007, 11:35 PM
Or

=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--((MA="1X")+(MA="1Z")+(MA="3L")))

Bob Phillips
08-29-2007, 11:36 PM
or even

=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,{"1X","1Z","3L"},0))))

mperrah
08-30-2007, 07:41 AM
Thanks to all, especially Bob, you da man
I went with post #7

What I plan to do is make a named range with the ma #'s spelled out for each office, then have a drop down validation list that gets called from the sumproduct
ie: named "modesto" range: (MA="1X")+(MA="1Z")+(MA="3L")
named "office" dataval: modesto, sac...
=sumproduct(.....(Office)
Trying this is not working (Value error)
I have 23 offices and need a way to input the different MA numbers
into the sumproduct

Post 7 is how the result needs to work, how can I store each group and use a named range in the sumproduct?

mperrah
08-30-2007, 07:57 AM
These are the offices and MA numbers I need to sum

Carson City, 1E, 3J, 3I, 3K, 1S, 1U, OA, OB, OC, OE, OD, OFModesto, 1Z, 1X, 3LSacramento, 1WSusanville, 1D, 7E, 7PBend, 18Eugene, 12, 13, 14, BH, BIMedford, 15, 16, 17, BL, BO, BJ, BK, BMNorth Coast, 1C, BQRedding, 1A, 19, 1BBakersfield, 23, 97, 98Fresno, 28, 58, 93, 20, 1YPrunedale, 21, 1Q, 1FVisalia, OG, 59, 94Hesperia, 9, 1P, BS, 2B, BV, 1V, 7VLas Vegas, 2, 4, 2M, 3H, OHSouth Coast, 22

Bob Phillips
08-30-2007, 09:14 AM
I am not really sure what you are doing. Do you need to test MA against the Carson City codes ANd the Sacramento codes AND the Susanville codes, etc. or somehow just agiant 1 set depending on something?

Whatever way, I think #8 would be a better formula.

mperrah
08-30-2007, 09:35 AM
I have a sheet with quality control reports from many city offices.
Each office has several Management Areas they cover (Last Post)
I need to alter the chart to show one office at a time, wich would include multiple MA's
I don't need to chart 2 offices at the same time.
So I thought if I could make a validation list of the offices that would populate the sumproduct I'm using for the chart, I could use a named range for the list of offices with the MA values listed for each and use the variable in the sumproduct for each office and grouped MA's

sumproduct(--(ma=officename)
ma= the column on the source data
officename= the validation list of group ma values

Does that make sense?
Mark

Bob Phillips
08-30-2007, 09:54 AM
Yeah, which is where I said #8 would work better for you

=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,officename,0))))

mperrah
08-30-2007, 01:24 PM
I'm having trouble setting up the validation list
I would like the name of the shop to appear in the list,
but the value resulting is the MA number letter combos to be used in the sumproduct,

I have this in a cell:
"1Z","1X","3L"
and use a data Val to pick this combo
The dataval is named officeChoice
This is one the chart source:
=SUMPRODUCT(--(item27=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,officeChoice,0))))
Also I saw the ISNUMBER, does this work on letters too,
some of the MA's have just number where others are number and letter (1X)?

mperrah
08-30-2007, 01:28 PM
Attached file
These are copied sheets so not all formulas work,
but the chart and office names are in place...

Bob Phillips
08-30-2007, 01:40 PM
It's very difficult to seriously help when the worksheet doesn't work at all. There are numes referred to in the worksheet that just don't exist.

It isn't ISNUMBER that is checking, it is the MATCH. ISNUMBER validates whether MATCH worked or not.

The MA codes should not all be in one cell.

mperrah
08-30-2007, 03:20 PM
Here is the full version
It was a big file and I was not sure it would zip to the min size.

Bob Phillips
08-30-2007, 04:19 PM
Yu know what, my eyesight ain't bad, but ARial 6pt at 80% is a tad hard to read, you've still got undefined names, and what am I supposed to be looking for?

mperrah
08-30-2007, 08:17 PM
Sorry Bob,
I'm used to zooming in and out.
The Sheet "Chart" has the area I need help.
Cell AR29 has the validation list with the management areas (MA)
AD5 has the list I would like to be the name of the offices,
and I can use a dependant list that changes the value of the MA
which then gets called in the sumproduct in B2:AB2
I tried your code in AB2 to see what happens.

This version worked :((MA="1X")+(MA="1Z")+(MA="3L")
I'm just not sure how to make a validation list that uses this format
and can go into the sumprodcut.

mperrah
08-30-2007, 11:41 PM
We Got IT!
Thank you Bob!
Re-reading post 18 said values can't be in same cell.
I put each value in a different cell and named that range.
plugged the name in the sumproduct and presto.
Now I made a named range for each office,
and typed the names of each of those named ranges
into another range named MAList
then made a dataVal list with the name MAListChoice
in sumproduct is the MAListChoice:
=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,MAListChoice,0))))

You are awesome!!!

Sorry again about the small font

I got ahead of myself,
The named range of the named range doesn't calculate
If I code the MAListModesto in the sum product it works,
but if I have a DataVal list with MAListModesto as the picked choice it does not work.
I tried:
=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,MAListChoice,0))))

and
=SUMPRODUCT((item01=scan_item)*(QCDate>=chStart)*(QCDate<=chEnd)*(ISNUMBER(MATCH(MA,MAListChoice,0))))

and
=SUMPRODUCT((item01=scan_item)*(QCDate>=chStart)*(QCDate<=chEnd)*(MA=MAListChoice))

How can I make a list to choose from to input the picked values for the sumproduct?

Bob Phillips
08-31-2007, 08:37 AM
Are you saying that you have one DV with a list of range names, and you want to pick one from that list, and for SUMPRODUCT to use that selected name in its formula?

If so, I think you want

=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,INDIRECT(MAListChoice),0))))

mperrah
08-31-2007, 10:10 PM
That is exactly what I'm aiming for
Does it matter that the dv has a different number of items from the rest of the sumproduct?
I tried * in between too and I get a #Num error

=SUMPRODUCT((item27=scan_item)*(QCDate>=chStart)*(QCDate<=chEnd)*(ISNUMBER(MATCH(MA,INDIRECT(maListChoice),0)))) I noticed the file I zipped for you did have problems with names.
I'm revising an older version before the names were lost.

Thanks so much for your patience and persistence Bob.
Mark

Bob Phillips
09-01-2007, 01:29 AM
It doesn't matter that MAChoiceList is a different size, but MA must be the same size.

So did the formula I gave not work?

ARe you going to post a new workbook?

mperrah
09-01-2007, 10:34 AM
Attached is a working file... completely
your Indirect did the trick
I had some named range problems, I found the use of offset instead of naming the entire column.
The calculating was taking a very long time with every change.
Reseting the names to an offset fixed everything.
Your code works like a charm.
I moved all the named ranges to a sheet("library")
The sheet("chart") has the pie chart and the sumproduct and the dataval drop down for the date start and stop and the office choice (MA)
The source data evrything is mapping from is on sheet("QCDetail")
just change the office and dates, and the chart updates.

Thank you so so much Bob.:bow: :friends:

Humbly, Mark

Bob Phillips
09-01-2007, 10:36 AM
Excellent, dynamic range names are a very useful tool.

Good on you mperrah.

mperrah
09-01-2007, 11:40 AM
If you can see any other fixes to make the macro's run faster let me know.
I am going to making 19 copies of this file for different offices and one master file to compile all the qcDetails and chart everything.
I'm just very happy this is working.
Thank you again Bob.
Mark