SUMIFS if final Criteria is a range

[GDBR]

Hi,
I am using Excel 2007 with Windows XP. I need to sum a over a range of cells with the final criteria being a range.
My formula is
=SUMIFS('1. Reserves'!$E$3:$E$2000,'1. Reserves'!$A$3:$A$2000,$C10,'1. Reserves'!$B$3:$B$2000,$D10,'1. Reserves'!$C$3:$C$2000,$E10,'1. Reserves'!$D$3:$D$2000,$G10:$H10)

How do I do this?

Thanks,
Gavin

 

[Haseeb Avarakkan]

Use SUMIFS inside the SUMPRODUCT. Like,

=SUMPRODUCT(yourformula)

 

[GDBR]

Hi Haseeb,

This only sums the 2 cells not the cells in between as well. What I meant to say it only sums the two cells on the outside of the array not the ones inbetween

Gavin

 

[Haseeb Avarakkan]

not the cells in between

Sorry I didn't understand what do you mean? There is no cells between G10:H10.

sums the two cells on the outside of the array not the ones inbetween

Which array? I don't understand what do you mean? If you can post some sample data, could be helpfull.

 

[Aladin Akyurek]

Hi,
I am using Excel 2007 with Windows XP. I need to sum a over a range of cells with the final criteria being a range.
My formula is
=SUMIFS('1. Reserves'!$E$3:$E$2000,'1. Reserves'!$A$3:$A$2000,$C10,'1. Reserves'!$B$3:$B$2000,$D10,'1. Reserves'!$C$3:$C$2000,$E10,'1. Reserves'!$D$3:$D$2000,$G10:$H10)

How do I do this?

Thanks,
Gavin

Do you mean that $G10:$H10 must hold for '1. Reserves'!$D$3:$D$2000?

 

[GDBR]

Hi,
I can't work out how to put an exert of my spreadsheet into the post, so I will try and explain what I mean.
G10:H10 refer to some cells that have the numbers 11 and 15 (these can change) the formula then looks at another workbook where I need to sum the range 11 to 15.

Gavin

 

[Aladin Akyurek]

Hi,
I can't work out how to put an exert of my spreadsheet into the post, so I will try and explain what I mean.
G10:H10 refer to some cells that have the numbers 11 and 15 (these can change) the formula then looks at another workbook where I need to sum the range 11 to 15.

Gavin

Looks like...

=SUMIFS('1. Reserves'!$E$3:$E$2000,'1. Reserves'!$A$3:$A$2000,$C10,'1. Reserves'!$B$3:$B$2000,$D10,'1. Reserves'!$C$3:$C$2000,$E10,'1. Reserves'!$D$3:$D$2000,">="&$G10,'1. Reserves'!$D$3:$D$2000,"<="&$H10)

 

[GDBR]

You are a champion that works perfectly.

Gavin