Adding in an OR statement to a sum product function

[courtney27]

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.

 

[Marcelo Branco]

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.

 

[courtney27]

Thanks Marcelo, this worked well. Unfortunately, my data set is huge and the actual number of rows is 1048576.
Thanks again!

 

[Marcelo Branco]

You are welcome.

Good luck!

M.

 

[Aladin Akyurek]

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