Adding in an OR statement to a sum product function

courtney27

New Member
Joined
Dec 17, 2014
Messages
12
Hello,

I am trying to write a sum product formula that also includes an OR statement. The value I would like to return is a MatchID based on a Season, Round and Team, however the Team could be one of 2 teams - hence the need for an OR statement. Please see below an example of my data for both the reference table and the sum product table with column references above. Both tables have the same number of rows (1048576).


A C D G
Season Round MatchID Team
2003 16 ? EFC
2004 17 ? BFC
2005 3 ? CFC

V W Y Z AD
Season Round Team1 Team2 MatchID
2003 16 COFC EFC 5
2004 17 BFC PAFC 10
2005 3 FRFC CFC 9

Here is what I have tried so far (which returns a "0"). I read on a forum that the "+" could be used to simulate the "OR".
=SUMPRODUCT(V2:V1048576=A2)*(W2:W1048576=C2)*(Y2:Y1048576=G2)+(Z2:Z1048576=G2)*(AD2:AD1048576)

Please let me know where I am going wrong and any suggestions for getting the right answer. Thank you for your help.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,405
Try to avoid references to entire columns, or almost, like V2:V1048576. This type of reference to huge ranges seriously impair the spreadsheet performance. Try to limit the ranges to the actual or the maximum expected number of rows.

To answer your question, in the formula below i assumed the data in rows 2:1000 - adjust to your real case.

Maybe this...

Formula in D2 copied down
=SUMPRODUCT(--($V$2:$V$1000=A2),--($W$2:$W$1000=C2),--((($Y$2:$Y$1000=G2)+($Z$2:$Z$1000=G2))>0),$AD$2:$AD$1000)

Hope this helps

M.
 
Last edited:

courtney27

New Member
Joined
Dec 17, 2014
Messages
12
Thanks Marcelo, this worked well. Unfortunately, my data set is huge and the actual number of rows is 1048576.
Thanks again!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thanks Marcelo, this worked well. Unfortunately, my data set is huge and the actual number of rows is 1048576.
Thanks again!

In AE2 (or in a cell more convenient) enter and copy down:

=V2&"|"&W2&"|"&Y2&"|"&Z2

When done, try to run:

=SUMIFS($AD$2:$AD$1000,$A$2:$A$1000,A2&"|"&C2&"|"&"*"&G2&"*")

This set up trades off space (memory) against time (speed).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,229
Messages
5,594,944
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top