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"))

 

[etaf]

same range ?
so you would need to change to a SUMIFS()

SUMIF ( range, criteria , sum range)
SUMIFS( sum range , range1, criteria1 , range2, criteria2 )

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

Becomes

SUMIFS( INDIRECT("'"&$C$3&"'!h:h") ,

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

 

[colinheslop1984]

Hi

Yeh the SUMIF version worked fine, I have rewritten it as follows but it returns value '0'. There is definitely a value in the cells I am referencing

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

To give you an idea, I have worksheets per year, 2017, 2018, etc. C3 refences these, G3 references week numbers in column C of each worksheet, written exactly as they are on the worksheets and A11 references the day of the week found in column D

Any ideas?

 

[etaf]

a sumi() is usually a
SUMIF ( range, criteria , sum range)
so that means
=SUMIF(INDIRECT("'"&$C$3&"'!d:d"),A:A,INDIRECT("'"&$C$3&"'!h:h"))
= 2017!D:D . - Range for Criteria
= A:A - which is the criteria to search for - BUT A:A is a range
= 2017!H:H - the range to SUM

what is your criteria you are looking up in column D

the original formula NOT working for me

 

[colinheslop1984]

Column C is week number, Column D is day of the week.

Column A:A has the days Sun - Sat

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

In this example I tried to be more specific using A11 for the day of the week criteria to see if it made a difference. It didnt.

 

[etaf]

strange A:A is not a criteria but a range

SUMIFS( sum range , range1, criteria1 , range2, criteria2 )

if you do it without any indirect
SUM Range is H:H
Criteria Range1 is C:C . Criteria for C:C is ??
Criteria Range2 is D:D Criteria for D:D is ??

try it hard coded

 

[colinheslop1984]

Weird, it returns the value '0' again, I know for sure that the formula is correct

=SUMIFS('2019'!H:H,'2019'!C:C,"'Weekly Sales'!G3",'2019'!D:D,"'Weekly Sales'!A11")

 

[colinheslop1984]

OK I have managed to get a result by using "="& to reference the criteria when hard coded, but I dont get a result when I apply the same idea to the original formula

Weird, it returns the value '0' again, I know for sure that the formula is correct

=SUMIFS('2019'!H:H,'2019'!C:C,"'Weekly Sales'!G3",'2019'!D:D,"'Weekly Sales'!A11")

 

[etaf]

OK, so thats different to previous posts

=SUMIFS('2019'!H:H,'2019'!C:C,"'Weekly Sales'!G3",'2019'!D:D,"'Weekly Sales'!A11")

TO check
in range C:C do you have G3 and also ON the same ROW in D:D do you have A11 - and if you do then whats in the same row on H:H

OR
put in test values to check
then CHANGE to indirect


BUT this is not the same
=SUMIFS(INDIRECT("'"&$C$3&"'!H:H"),INDIRECT("'"&$C$3&"'!c:c",$G$3),INDIRECT("'"&$C$3&"'!d:d",A11))
AS
=SUMIFS('2019'!H:H,'2019'!C:C,"'Weekly Sales'!G3",'2019'!D:D,"'Weekly Sales'!A11")

ALSO you have
"'Weekly Sales'!G3"
NOTE the inverted commas

try
=SUMIFS('2019'!H:H,'2019'!C:C,'Weekly Sales'!G3,'2019'!D:D,'Weekly Sales'!A11)

 

[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