Stringing a Criteria Together

**rosarionyc** · 11-24-2010, 03:28 PM

Hello Guys-

Here goes another one of those string formulas I love.

=IFERROR(AVERAGEIFS('3-Service Calculator'!$L:$L,'3-Service Calculator'!$G:$G,"1-serv call",'3-Service Calculator'!$X:$X, 3)/24,0)

Id like to add the following criteria’s please’

'3-Service Calculator'!$L:$L,'3-Service Calculator'!$G:$G,"3-recall"
'3-Service Calculator'!$L:$L,'3-Service Calculator'!$G:$G,"4-more time"
'3-Service Calculator'!$L:$L,'3-Service Calculator'!$G:$G,"6-follow up"
Thanks! =)

**NBVC** · 11-24-2010, 03:38 PM

Try:

=IFERROR((SUMPRODUCT((('3-Service Calculator'!$G$1:$G$10000="1-serv call")+('3-Service Calculator'!$G$1:$G$10000="3-recall")+('3-Service Calculator'!$G$1:$G$10000="4-more time")+('3-Service Calculator'!$G$1:$G$10000="6-follow up")),--('3-Service Calculator'!$X$1:$X$10000=3),'3-Service Calculator'!$L$1:$L$10000)/SUMPRODUCT((('3-Service Calculator'!$G$1:$G$10000="1-serv call")+('3-Service Calculator'!$G$1:$G$10000="3-recall")+('3-Service Calculator'!$G$1:$G$10000="4-more time")+('3-Service Calculator'!$G$1:$G$10000="6-follow up")),--('3-Service Calculator'!$X$1:$X$10000=3)))/24,0)

**rosarionyc** · 11-24-2010, 03:54 PM

Originally Posted by NBVC

Try:

=IFERROR((SUMPRODUCT((('3-Service Calculator'!$G$1:$G$10000="1-serv call")+('3-Service Calculator'!$G$1:$G$10000="3-recall")+('3-Service Calculator'!$G$1:$G$10000="4-more time")+('3-Service Calculator'!$G$1:$G$10000="6-follow up")),--('3-Service Calculator'!$X$1:$X$10000=3),'3-Service Calculator'!$L$1:$L$10000)/SUMPRODUCT((('3-Service Calculator'!$G$1:$G$10000="1-serv call")+('3-Service Calculator'!$G$1:$G$10000="3-recall")+('3-Service Calculator'!$G$1:$G$10000="4-more time")+('3-Service Calculator'!$G$1:$G$10000="6-follow up")),--('3-Service Calculator'!$X$1:$X$10000=3)))/24,0)

For some reason it is giving me the same answer that this is giving me. Im sure excel is right and im just overlooking my own manual calculation.

I will send you an email with the document and were to look in a bit.

=IFERROR(AVERAGEIFS('3-Service Calculator'!$L:$L,'3-Service Calculator'!$G:$G,{"1-serv call","3-recall","4-more time","6-follow up"},'3-Service Calculator'!$X:$X, 1)/24,0)

**NBVC** · 11-24-2010, 04:05 PM

If you are getting the same answer than you have matches only for the first condition (i.e. '3-Service Calculator'!$G:$G="1-serv call", I think. The AVERAGEIFS formula written that way wouldn't work.