SumIfs Using Indirect based on 2 Criteria

[MINDFULLYLIZ]

Hi! I have been struggling for 5 days trying to get this formula to work. Basically, I need it to search across a few worksheets which I have named "JON" and sum a corresponding cell if it meets two conditions.

The formula returns a "Value" error:

=SUMIFS(INDIRECT("'"&JON&"'!$F$16:$F$65"),INDIRECT("'"&JON&"'!$A$16:$A$65"),$A5,INDIRECT("'"&JON&"'!A1:A1"),$B5))

Please tell me what I'm doing wrong.

Also note on the second criteria I've changed the "A1:A1" to just A1 and A1:A2 and A1:B2 to see if that helps

 

[kweaver]

Welcome to MrExcel.

Why do you need to use INDIRECT? Your formula returns an error because JON has no value. Is that the name of the sheet?

SUMIFS(JON!$F$16:$F$65,criteria1,JON!$A$!6:$A$65,criteria2,JON!$A$1,$B5)

Is the third criteria that JON!A1 = B5 ?
It's not clear to me what the 1st and 2nd criteria are.

If you presented a sheet using XL2BB, it would help understanding your objectives.

 

[MINDFULLYLIZ]

Hi and thank you for helping. I thought I had to use the indirect function in order to reference the different worksheets?

The first criteria is JON!A1=B5
and the second is JON!A16:A65 =A5
the sum range would be JON!F16:F65

 

[MINDFULLYLIZ]

I entered this =SUMIFS(JON!$F$16:$F$65,JON!$A$16:$A$65,$A5,JON!$A$1,$B5)

and I get this error:

 

[kweaver]

I don't quite understand your logic, but try this:

=SUMPRODUCT((JON!A1=B5)*(JON!A16:A65=A5)*(JON!F16:F65))

It seems that if A1 <> B5 the sum will be 0. Is that right?

If this is all run on the JON sheet, you don't need to use the JON reference. Just:

=SUMPRODUCT((A1=B5)*(A16:A65=A5)*(F16:F65))

 

[kweaver]

If you posted a sheet using XL2BB, it would help

 

[Fluff]

@MINDFULLYLIZ
With SUMIFS all the criteria ranges must be the same size, which is why you formula doesn't work.
Can you post some sample data using the XL2BB add-in?

@kweaver
I think that JON is a range name which has the sheet names the formula should look at.

 

[Fluff]

If you have an additional range name next to the JON range that shows whether the sheet should be included, you could use

=SUMPRODUCT((Incl=B5)*(SUMIF(INDIRECT("'"&JON&"'!A16:A65"),A5,INDIRECT("'"&JON&"'!F16:F65"))))

Where Incl is the new range name.

 

[MINDFULLYLIZ]

If you have an additional range name next to the JON range that shows whether the sheet should be included, you could use

=SUMPRODUCT((Incl=B5)*(SUMIF(INDIRECT("'"&JON&"'!A16:A65"),A5,INDIRECT("'"&JON&"'!F16:F65"))))

Where Incl is the new range name.

Hi! I used this formula as : =SUMPRODUCT(("'"&JON&"'!$A$1"=B5)*(SUMIF(INDIRECT("'"&JON&"'!A16:A65"),A5,INDIRECT("'"&JON&"'!F16:F65"))))

It's the first formula that hasn't returned an error, resulted in "0" answer should be 100 - but I'm going to play w/this and see what it's not picking up

 

[Fluff]

Try it like

=SUMPRODUCT((COUNTIF(INDIRECT("'"&JON&"'!A1"),B5))*(SUMIF(INDIRECT("'"&JON&"'!A16:A65"),A5,INDIRECT("'"&JON&"'!F16:F65"))))