SUMPRODUCT with INDIRECT and multiple criteria

[Civitas]

Hi all,
My first post so I hope I've done it in accordance with the rules of the board.

Question: I have a spreadsheet for football results. So I've got teams on different sheets pulling the data from sheets.

I've been using the first formula with no problems but decided I needed away of being able to update the data from different cells on the same sheet. So, I introduced 'INDIRECT' to facilitate this, at the same time used some name ranges.

So to the problem. Although the first two parts of the sumproduct

1.
=SUMPRODUCT(('10-11'!$D$2:$D$307="Dortmund")*('10-11'!$H$2:$H$307=D17)*('10-11'!$AN$2:$AN$307<0.2))

=SUMPRODUCT((--(INDIRECT("'"&$C$4&"'!"&AC18)=team))*(--(INDIRECT("'"&$C$4&"'!"&AD18)=PAHome1))*(--(INDIRECT("'"&$C$4&"'!"&AE18)=AG18)))

 

[pedrowave]

Hi Civitas, the first formula with INDIRECT function:

=SUMPRODUCT((INDIRECT("'"&$C$4&"'!$D$2:$D$307")="Dortmund")*(INDIRECT("'"&$C$4&"'!$H$2:$H$307")=D17)*(INDIRECT("'"&$C$4&"'!$AN$2:$AN$307")<0.2))

Doing C4 cell ='10-11

To make the second formula works:

[COLOR=#333333]=SUMPRODUCT((--(INDIRECT("'"&$C$4&"'!"&AC18)=team))*(--(INDIRECT("'"&$C$4&"'!"&AD18)=PAHome1))*(--(INDIRECT("'"&$C$4&"'!"&AE18)=AG18)))[/COLOR]

Write next data into these cells:
C4 =10-11
AC18 =$D$2:$D$307
AD18 =$H$2:$H$307
AE18 =$AN$2:$AN$307

I hope that it will help to you.

Visit my blog: Pedro Wave for Excel Guys

 

[Civitas]

ah, yes that is already done within the spread sheet. But if I use the first version it works substituting the second one it doesn't. I've worked out that it's the last part, AG18 that doesn't. So the first two parts let me use cell names for the calculations but the last part of the formula that contains =AG18 doesn't work and I have to use <0.2.
This of course is at cell AG18.

I hope this helps anyone looking on.

 

[pedrowave]

If AG18 =0.2 works this formula?

[COLOR=#333333]=SUMPRODUCT((--(INDIRECT("'"&$C$4&"'!"&AC18)=team))*(--(INDIRECT("'"&$C$4&"'!"&AD18)=PAHome1))*(--(INDIRECT("'"&$C$4&"'!"&AE18)[/COLOR][B][COLOR=#333366][FONT=arial]<[/FONT][/COLOR][/B][COLOR=#333333][B]AG18[/B])))[/COLOR]

<ag18)))< font="">
Or you write AG18 =<0.2 ???</ag18)))<>