Sumif using Arrays

[Ruchi A.]

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:B10)

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

Any suggestions? thanks,

R.A.

 

[Infinitogool]

Ruchi A.

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

Regards,
Pedro J.

 

[Teethless mama]

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