Add one condition to this formula...?

[jaedmar]

This is the formula that I currently use:

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!m15"),"FF",INDIRECT("'"&sheetlist&"'!m22")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!m15"),"FF")),"")

What it does is average the m22 cells on sheets in which m15=FF.

In addition to the condition that m15 must = FF, I want to add a second condition before averaging. I want to add the condition that m22 must be > 1.

How do I add that condition to the above formula?

 

[Aladin Akyurek]

This is the formula that I currently use:

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!m15"),"FF",INDIRECT("'"&sheetlist&"'!m22")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!m15"),"FF")),"")

What it does is average the m22 cells on sheets in which m15=FF.

In addition to the condition that m15 must = FF, I want to add a second condition before averaging. I want to add the condition that m22 must be > 1.

How do I add that condition to the above formula?

Try...

=IFERROR(SUMPRODUCT(SUMIFS(
     INDIRECT("'"&sheetlist&"'!m22"),
     INDIRECT("'"&sheetlist&"'!m22"),">1",
     INDIRECT("'"&sheetlist&"'!m15"),"FF"))/
   SUMPRODUCT(COUNTIFS(
     INDIRECT("'"&sheetlist&"'!m22"),">1",
     INDIRECT("'"&sheetlist&"'!m15"),"FF")),"")

 

[jaedmar]

Thanks, that worked.

One more question. How would I add that same extra condition to the following formula (to make a total of three conditions):

=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&sheetlist&"'!m22"),INDIRECT("'"&sheetlist&"'!m8"),">=35",INDIRECT("'"&sheetlist&"'!m8"),"<40"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&sheetlist&"'!m8"),">=35",INDIRECT("'"&sheetlist&"'!m8"),"<40")),"")

In order to get the average, three conditions must exist: m22 should be > 1, and the person's age in m8 should be >=35 but <40.

 

[T. Valko]

Thanks, that worked.

One more question. How would I add that same extra condition to the following formula (to make a total of three conditions):

=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&sheetlist&"'!m22"),INDIRECT("'"&sheetlist&"'!m8"),">=35",INDIRECT("'"&sheetlist&"'!m8"),"<40"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&sheetlist&"'!m8"),">=35",INDIRECT("'"&sheetlist&"'!m8"),"<40")),"")

In order to get the average, three conditions must exist: m22 should be > 1, and the person's age in m8 should be >=35 but <40.

Try this...

=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&sheetlist&"'!M22"),INDIRECT("'"&sheetlist&"'!M22"),">1",INDIRECT("'"&sheetlist&"'!M8"),">=35",INDIRECT("'"&sheetlist&"'!M8"),"<40"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&sheetlist&"'!M22"),">1",INDIRECT("'"&sheetlist&"'!M8"),">=35",INDIRECT("'"&sheetlist&"'!M8"),"<40")),"")

 

[Aladin Akyurek]

Thanks, that worked.

You are welcome.

One more question. How would I add that same extra condition to the following formula (to make a total of three conditions):

=IFERROR(SUMPRODUCT(SUMIFS(
INDIRECT("'"&sheetlist&"'!m22"),
INDIRECT("'"&sheetlist&"'!m8"),">=35",
INDIRECT("'"&sheetlist&"'!m8"),"<40"))/
SUMPRODUCT(COUNTIFS(
INDIRECT("'"&sheetlist&"'!m8"),">=35",
INDIRECT("'"&sheetlist&"'!m8"),"<40")),"")

In order to get the average, three conditions must exist: m22 should be > 1, and the person's age in m8 should be >=35 but <40.

You were almost there. I think you forgat to keep the expression INDIRECT("'"&sheetlist&"'!m22"),">1" in both the nominator and denomitor from the eaarlier SumIfs formula...

=IFERROR(SUMPRODUCT(SUMIFS(
     INDIRECT("'"&sheetlist&"'!m22"),
     INDIRECT("'"&sheetlist&"'!m22"),">1",
     INDIRECT("'"&sheetlist&"'!m8"),">=35",
     INDIRECT("'"&sheetlist&"'!m8"),"<40"))/
   SUMPRODUCT(COUNTIFS(
     INDIRECT("'"&sheetlist&"'!m22"),">1",
     INDIRECT("'"&sheetlist&"'!m8"),">=35",
     INDIRECT("'"&sheetlist&"'!m8"),"<40")),"")

I suppose the test INDIRECT("'"&sheetlist&"'!m15"),"FF" the earlier formula has is not needed!