#VALUE error on a SUMIFS formula

[Pauljj]

It might be the heat getting to me but for the life of me can't work out why this won't work

=SUMIFS(Sheet1!$D$2:$AR$1000,Sheet1!$A1:$A$1000,Sheet2!C$2,Sheet1!$D$1:$AR$1,Sheet2!$A13)

This produces a #VALUE error

I am summing everything in Sheet 1 D2:AR1000 if the location in Sheet A1:A1000 equals Sheet 2 C2 and if Sheet 1 D1:AR1 contains what is in Sheet 2 A13

Seems simple enough ?

 

[DanteAmor]

Try this:

=SUMPRODUCT((Sheet1!$A2:$A$1000=Sheet2!C$2)*(Sheet1!$D$1:$AR$1=Sheet2!$A13)*(Sheet1!$D$2:$AR$1000))

 

[Pauljj]

Try this:

=SUMPRODUCT((Sheet1!$A2:$A$1000=Sheet2!C$2)*(Sheet1!$D$1:$AR$1=Sheet2!$A13)*(Sheet1!$D$2:$AR$1000))

Thanks so much, works a treat, should have realised that I was trying to check against rows and columns rather then just rows

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.