Solved: Does sumproduct work with OR

[mperrah]

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]

=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]

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]

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]

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]

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

[Bob Phillips]

Or

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

[Bob Phillips]

or even

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

[mperrah]

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]

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]

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]

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]

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]

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:
[VBA]"1Z","1X","3L"[/VBA]
and use a data Val to pick this combo
The dataval is named officeChoice
This is one the chart source:
[VBA]=SUMPRODUCT(--(item27=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,officeChoice,0))))[/VBA]
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]

Attached file
These are copied sheets so not all formulas work,
but the chart and office names are in place...

[Bob Phillips]

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]

Here is the full version
It was a big file and I was not sure it would zip to the min size.

[Bob Phillips]

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]

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]

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:
[vba]=SUMPRODUCT(--(item01=scan_item),--(QCDate>=chStart),--(QCDate<=chEnd),--(ISNUMBER(MATCH(MA,MAListChoice,0))))[/vba]

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:
[vba]=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=MAList Choice))[/vba]

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