# shorten sumproduct formula

#### mycroft99

##### New Member
Does anyone know how I can reduce or clean up this formula? It works but it looks like a dogs breakfast.

Essentially I want 10 conditions to be met before summing a particular range i.e. Data!\$F\$4:\$GC\$48. All conditions are the same except the 4th condition which varies from = \$A\$6 to = \$A\$12, and I have just added an additional 6 Sumproducts, changing the 4th reference each time.

Is there a way to have the 4th condition read "\$A\$6 or \$A\$7 or \$A\$8" etc..?

=((SUMPRODUCT((Data!\$C\$4:\$C\$48=B\$4)*(Data!\$E\$4:\$E\$48=\$A34)*(Data!\$F\$2:\$GC\$2=B\$3)*(Data!\$F\$3:\$GC\$3=\$A\$6),Data!\$F\$4:\$GC\$48)+SUMPRODUCT((Data!\$C\$4:\$C\$48=B\$4)*(Data!\$E\$4:\$E\$48=\$A34)*(Data!\$F\$2:\$GC\$2=B\$3)*(Data!\$F\$3:\$GC\$3=\$A\$7),Data!\$F\$4:\$GC\$48)+SUMPRODUCT((Data!\$C\$4:\$C\$48=B\$4)*(Data!\$E\$4:\$E\$48=\$A34)*(Data!\$F\$2:\$GC\$2=B\$3)*(Data!\$F\$3:\$GC\$3=\$A\$8),Data!\$F\$4:\$GC\$48)+SUMPRODUCT((Data!\$C\$4:\$C\$48=B\$4)*(Data!\$E\$4:\$E\$48=\$A34)*(Data!\$F\$2:\$GC\$2=B\$3)*(Data!\$F\$3:\$GC\$3=\$A\$9),Data!\$F\$4:\$GC\$48)+SUMPRODUCT((Data!\$C\$4:\$C\$48=B\$4)*(Data!\$E\$4:\$E\$48=\$A34)*(Data!\$F\$2:\$GC\$2=B\$3)*(Data!\$F\$3:\$GC\$3=\$A\$10),Data!\$F\$4:\$GC\$48)+SUMPRODUCT((Data!\$C\$4:\$C\$48=B\$4)*(Data!\$E\$4:\$E\$48=\$A34)*(Data!\$F\$2:\$GC\$2=B\$3)*(Data!\$F\$3:\$GC\$3=\$A\$11),Data!\$F\$4:\$GC\$48)+SUMPRODUCT((Data!\$C\$4:\$C\$48=B\$4)*(Data!\$E\$4:\$E\$48=\$A34)*(Data!\$F\$2:\$GC\$2=B\$3)*(Data!\$F\$3:\$GC\$3=\$A\$12),Data!\$F\$4:\$GC\$48))

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### hiker95

##### Well-known Member
mycroft99,

This is the only example of a Sumproduct Or formula that I have.

I am not sure how you could use this example with your formula.

SUMPRODUCT OR:
'=SUMPRODUCT(--(\$D\$5:\$D\$162={"cri","maj","enh","min"})*(\$J\$5:\$J\$162="DataContent")*(\$Y\$5:\$Y\$162))

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

#### Ron Coderre

##### MrExcel MVP
Try this:
Code:
``````=SUMPRODUCT((Data!\$C\$4:\$C\$48=B\$4)*(Data!\$E\$4:\$E\$48=\$A34)*(Data!\$F\$2:\$GC\$2=B\$3)*
ISNUMBER(MATCH(Data!\$F\$3:\$GC\$3,\$A\$6:\$A\$12,0)),Data!\$F\$4:\$GC\$48)``````
Is that something you can work with?

#### mycroft99

##### New Member
Hiker95,

Not sure how I would go about that. Didn't try it as Ron's method looked easier for me. Thanks for the link though.

Ron,

That's gold Jerry, GOLD! It worked a treat but I am curious as to the ISNUMBER(MATCH(Data!\$F\$3:\$GC\$3,\$A\$6:\$A\$12,0) function, where the data matched is Text and not Number.

I love this site.
m99

#### barry houdini

##### MrExcel MVP
It doesn't matter whether the data is text or numeric

The ISNUMBER function here refers to the result of the MATCH formula. If there is a match then MATCH function returns a number (the relative position of the match in the lookup array), otherwise it returns #N/A error. ISNUMBER tests that value.

Thanks all.
10-4.

1,106,002
Messages
5,508,721
Members
408,690
Latest member
Lip Renan

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...