Sumif using Arrays
 

Hi,

I want to use the sumif function with a criteria range and a sum range that
is over multiple worksheets. I don't want to use multiple sumif functions
because there are quite a few worksheets.

For example, I have the same range of cells I want to select over multiple
worksheets, and sum if the criteria is greater 5.

Worksheet Name Test
Criteria Range A1:A10
Sum Range B1:B10

Worksheet Name Test2
Criteria Range A1:A10
Sum Range B1:B10

I have tried to enter the formula as follows:

=SUMIF('Test:Test2'!A1:A10,""&5,'Test:Test2'!B1:B 10)

I keep getting a #VALUE even when I use ctrl-shift-enter to enter the
formula as an array.

Any suggestions? thanks,

R.A.

Sumif using Arrays
 

Ruchi A.

Try
=SUMPRODUCT(SUMIF(INDIRECT("Test"&{"","2"}&"!A1:A1 0"),"5",B1:B10))

Regards,
Pedro J.

Hi,

I want to use the sumif function with a criteria range and a sum range that
is over multiple worksheets. I don't want to use multiple sumif functions
because there are quite a few worksheets.

For example, I have the same range of cells I want to select over multiple
worksheets, and sum if the criteria is greater 5.

Worksheet Name Test
Criteria Range A1:A10
Sum Range B1:B10

Worksheet Name Test2
Criteria Range A1:A10
Sum Range B1:B10

I have tried to enter the formula as follows:

=SUMIF('Test:Test2'!A1:A10,""&5,'Test:Test2'!B1:B 10)

I keep getting a #VALUE even when I use ctrl-shift-enter to enter the
formula as an array.

Any suggestions? thanks,

R.A.

Sumif using Arrays
 

=SUM(SUMIF(INDIRECT("Test"&{"",2}&"!A1:A10"),"5", INDIRECT("Test"&{"",2}&"!B1:AB10")))



"Ruchi A." wrote:

Hi,

I want to use the sumif function with a criteria range and a sum range that
is over multiple worksheets. I don't want to use multiple sumif functions
because there are quite a few worksheets.

For example, I have the same range of cells I want to select over multiple
worksheets, and sum if the criteria is greater 5.

Worksheet Name Test
Criteria Range A1:A10
Sum Range B1:B10

Worksheet Name Test2
Criteria Range A1:A10
Sum Range B1:B10

I have tried to enter the formula as follows:

=SUMIF('Test:Test2'!A1:A10,""&5,'Test:Test2'!B1:B 10)

I keep getting a #VALUE even when I use ctrl-shift-enter to enter the
formula as an array.

Any suggestions? thanks,

R.A.