sumifs using indirect

[colinheslop1984]

Hello

I need to add a second condition in to this formula, the additional criteria is in cell G3. Where/how would I add this in?

=SUMIF(INDIRECT("'"&$C$3&"'!d:d"),A:A,INDIRECT("'"&$C$3&"'!h:h"))

 

[colinheslop1984]

"weekly sales!G3" just appeared as I was flicking back and forth between work sheets to select ranges.

This is what I have which works

=SUMIFS('2019'!H2:H365,'2019'!C2:C365,"="&G3,'2019'!D2:D365,"="&A:A)

I just need to adapt it to include the indirect function now

C:C DOES contain G3 and on the same row in D:D I do have A11

The data source (2019) is like this:

A B C D E F G H
QTR MTH WK DAY DATE SALES
1 JUNE 1 SUN 03/06/18 3000
1 JUNE 1 MON 04/06/18 2000

ETC

That didnt come out as well as I thought it would!

A /// B /// C /// D /// E /// F /// G /// H
QTR MTH WK DAY DATE SALES
1 JUNE 1 SUN 03/06/18 3000
1 JUNE 1 MON 04/06/18 2000

 

[etaf]

still dont understand how a criteria can be a range "="&A:A
you dont need the =
So
'2019'!D2:D365,"="&A:A
Should be
'2019'!D2:D365,'Weekly Sales'!A11

shouldn't it?

 

[colinheslop1984]

Whether I use A:A or A11, I still get a result which is accurate.

But how come as soon as I apply the indirect function, in exactly the same formula (as far as I can tell), my result returns a zero, when it should be 40

 

[etaf]

OK, But i A:A should not work, or its picking up the first values in the rows 1/2

=SUMIFS('2019'!H:H,'2019'!C:C,"'Weekly Sales'!G3",'2019'!D:D,"'Weekly Sales'!A11")
This works
and you want to refer to the sheet for H:H , C:C & D:D
using a cell reference in same sheet as formula is in G3

=SUMIFS(INDIRECT("'"&$C$3&"'!H:H"),INDIRECT("'"&$C$3&"'!C:C"),$G$3,INDIRECT("'"&$C$3&"'!D:D"),A11)

SO in column H you have the items to addup
criteria range is C, criteria in G3
criteria range is D, criteria in A11

so in my example on
in F9
I have
=SUMIFS(INDIRECT("'"&$C$3&"'!H:H"),INDIRECT("'"&$C$3&"'!C:C"),$G$3,INDIRECT("'"&$C$3&"'!D:D"),A11)
and change cell C3 to
2019
OR
2018 C

try the link
https://www.dropbox.com/s/ed7fmurph11tnsp/Indirect_etaf.xlsx?dl=0