Thread: INDIRECT with two conditions in SUMIF

Hi

I've had success using this formula...

=SUMIF(INDIRECT($A3&"!CCY"),C$1,INDIRECT($A3&"!Value"))

where

A3 = sheet name (to link to another tab)
C1 = specified currency code eg USD
CCY = range specified in each tab relating to currency codes (eg USD)
Value = range specified in each tab relating to monetary values

The result successfully matches my chosen currency in cell C1 and sums the values of every occurrence of the currency in my chosen sheet.

BUT I want to extend this to add in another condition. I want to be able to link another condition using the indirect function which relates to a Buy ("B") or Sell ("S") tag which is also specified in each sheet. Hence I want to be able to say IF(INDIRECT($A3&"!BUY_SELL")="B") then do the summation for related the values for that currency.

I've attached a workbook to hopefully clarify things and have tried to set out what I need my summary sheet to look like.

Any suggestions welcome. I'd prefer to avoid pivot tables as the desire is to link to sheets which are copied in from other sources and need to be kept separated by date.

Thanks

Hello,

create another range per sheet,

BuySell ='30Aug2011'!$D:$D

Then you can use

=SUMPRODUCT(--(INDIRECT($A2&"!CCY")=C$1),--(INDIRECT($A2&"!BuySell")="S"),INDIRECT($A2&"!Value"))

But using Sumproduct with whole columns is not a good idea. You should limit the range names per sheet to only the populated rows.

Have a look at dynamic range names for that.

cheers,