Solved: Does sumproduct work with OR

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.

Code:

---

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

Code:

---

=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

Quote:

---

Originally Posted by mperrah

Code:

---

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

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

Quote:

---

Originally Posted by mperrah

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?

Quote:

---

Originally Posted by mperrah

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.

Quote:

---

Originally Posted by 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

---

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)))

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

Or

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

or even

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

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?

These are the offices and MA numbers I need to sum

Code:

---

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

---

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.

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

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))))

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)?

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

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.

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

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?

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.

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?

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))))

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

[vba]=SUMPRODUCT((item27=scan_item)*(QCDate>=chStart)*(QCDate<=chEnd)*(ISNUMBER( MATCH(MA,INDIRECT(maListChoice),0))))[/vba] 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

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?

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

Excellent, dynamic range names are a very useful tool.

Good on you mperrah.

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