Sumif using Arrays

R

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.
 
I

Infinitogool

Ruchi A.

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

Regards,
Pedro J.
 
T

Teethless mama

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top