Find multiple criteria and return sum of values

[maym]

Hi

I am trying to look up multiple criteria and return the sum of values next to them. Not sure if this can be achieved with INDEX MATCH or another formula.

I have attached an example.

My sheet has a number of tables relating to various teams and the venues they have played at in the past. When the next game comes up, I need to check how many games they have played at each venue and return that total. If it is mid way through the season, I also need to include these (see example attached).

If anyone can assist, that would be really appreciated

[Glenn Kennedy]

With a slight alteration to the data layout, this is very straightforward:

=SUMPRODUCT(('Venue History by Team'!$A$5:$A$92='Current Round'!$A5)*('Venue History by Team'!$B$5:$B$92='Current Round'!$E5)*('Venue History by Team'!$C$4:$AB$4<='Current Round'!$B$2)*'Venue History by Team'!$C$5:$AB$92)

[maym]

I think I just worked it out. Instead of "<=" , I used "<" but not sure if this is correct?

[maym]

ok thanks very much Glenn.

The values out to the end are the scheduled games for the rest of the season, however I only need to count up to current round.

Thanks again

[maym]

thanks Glenn, that has almost got it.

In the attached example, the first value for Team A is 23. If the current round is 5 as shown in the sheet, is there a way that the formula could exclude the 1 match played in round 5? I.e. for team A the total should be 22 (as they haven't played round 5 yet).

Thanks

[Glenn Kennedy]

Yep. That should do it. BtW... I meant to ask. If this is week 5, why are there values in the Table out to the end???

[Glenn Kennedy]

Great! You're welcome and thanks for the Rep.

[maym]

Hi

I have run this again but as you can see in the attached, I am getting mixed results. Some are correct, but those highlighted in yellow are different to the actual (correct amount).

Does anyone know why this would be happening?

[maym]

I keep trying but even though the formulas are the same, I receive different answers in the spreadsheet attached.

Can anyone assist me with this please?

[Glenn Kennedy]

Look at venue history by team N4 and X4. Amend them...

[maym]

got it now, thanks. Not sure how that happened!

Much appreciated

[maym]

thanks Glenn but I'm not sure that I follow?

[Glenn Kennedy]

No problem...