Need Help With SUMIFS FUNC

[Arun R]

Hi All,

I'm using SUMIFS with multiple criteria. I have multiple sheets which should be selected based on selection. So what i have done is build the reference in cells and refer it in SUMIFS. For Ex:

=SUMIFS(AM19,AN19,A20,AO19,Reference)

AM19 will have value like - Sheet1!$IR$4:$IR$137
AN19 will have value like - Sheet1!$QR$4:$QR$137
AO19 will have value like - Sheet1!$B$4:$B$137
Reference is a named criteria

When i see evaluate using calculation steps looks like cell values of AM19, AN19 and AO19 are not replaced as specified above SUMIFS takes it as cell reference and evaluate to 0. Suggestions welcome.

Thanks
Chine

 

[Nebu]

Hi:
Replace simple reference like AM19 with INDIRECT(AM19).

Thanks

 

[Arun R]

Hi Nebu,

Its not working. Can somebody help please.

Regards,
Arun

 

[Khalid NGO]

Its not working.

Hi Arun,

It should work as you described and Nebu advised.

=SUMIFS(INDIRECT(AM19),INDIRECT(AN19),A20,INDIRECT(AO19),Reference)

Reference = single cell or range?

Can somebody help please.

Post a sample file.

Regards,

 

[Arun R]

Hi All,

Let me just explain this way then:
=SUMIFS("'"&Reference&"'"!A:A,"'"&Reference&"'"!B:B,A20,"'"&Reference&"'"!C:C,Reference)

Reference is a "Name" changes in real time based on a List selection. I get an error in this. Any help is appreciated.

Regards,
Chine