SUMPRODUCT across multiple criteria and columns

[richardj]

Hi Guys,

Hope you can help, I am trying to sum up a column based on a number of criteria, having trawled the forum for a while I am using the following formula but it is returning N/A:

=SUMPRODUCT(('[data.xlsx]TY'!$A:$A=G15&J12&K1)*('[data.xlsx]TY'!$BC13:BM13=k2),'[data.xlsx]TY'!$BC21:$BM15309)

The first part is looking at a row to see if it matches the criteria, the second part is looking a column t see if that also matches and the 3rd part should sum up the appropriate column

Anyone spot where I'm going wrong?

Much appreciated!

[daddylonglegs]

You need the first range to match the rows in the last range, like this

=SUMPRODUCT(('[data.xlsx]TY'!$A21:$A15309=G15&J12&K1)*('[data.xlsx]TY'!$BC13:BM13=k2),'[data.xlsx]TY'!$BC21:$BM15309)

[richardj]

Hmm I still seem to be getting an N/A error

[daddylonglegs]

OK, you needed the first change anyway......but in fact the mismatched ranges would give a #VALUE! error so #N/A indicates that you have #N/A values in some of the ranges, presumably in the sum range '[data.xlsx]TY'!$BC21:$BM15309

Get rid of those and formula should work or you can make it work without eliminating them by using an array formula like this:

=SUM(IF('[data.xlsx]TY'!$A21:$A15309=G15&J12&K1,IF('[data.xlsx]TY'!$BC13:BM13=k2,IF(ISNUMBER('[data.xlsx]TY'!$BC21:$BM15309),'[data.xlsx]TY'!$BC21:$BM15309))))

confirmed with CTRL+SHIFT+ENTER

[richardj]

Cracked it! Thanks a million dadylonglegs!!!!