Multiple Criteria's

Hello guys-

I have these two formulas below that I wish to add 3 criterias to…

=SUMPRODUCT(--('3-Service Calculator'!$G$1:$G$10000="1-serv call"),--('3-Service Calculator'!$X$1:$X$10000=1),'3-Service Calculator'!$S$1:$S$10000)/24

Criteria =

G$1:$G$10000= "4-more time"
G$1:$G$10000= "3-recall"
G$1:$G$10000= "6-follow up"

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

Criteria =

G:G= "4-more time"
G:G= "3-recall"
G:G= "6-follow up"

Any help will be greatly appreciated. Thanks in advance for your assistance!

You mean you want to add if G1:G10000 is either of those conditions?

If so:

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

and

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

Note: Countifs, Sumifs, Averageifs don't take OR condtions....

Originally Posted by NBVC

You mean you want to add if G1:G10000 is either of those conditions?

If so:

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

and

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

Note: Countifs, Sumifs, Averageifs don't take OR condtions....

Hello and thank you. The 1st formula seem to work but i am not yet sure of it because there is a 2nd formula in which i need to alter as well. I tried to follow the format as described in the 1st formula but i got an error messege, this is why i am posting the 2nd formula for your review. Thanks!

=SUMPRODUCT(--('3-Service Calculator'!$G$1:$G$10000="7-part call"),--('3-Service Calculator'!$X$1:$X$10000=1),'3-Service Calculator'!$S$1:$S$10000)/24

Additional Criteria =

'3-Service Calculator'!$G$1:$G$10000="8-high part"

That criteria looks like the one in the formula already. Is that the right one?

Try:

=SUMPRODUCT((('3-Service Calculator'!$G$1:$G$10000="7-part call")+('3-Service Calculator'!$G$1:$G$10000="8-high part")),--('3-Service Calculator'!$X$1:$X$10000=1),'3-Service Calculator'!$S$1:$S$10000)/24

Try:

=IFERROR((SUMPRODUCT((('3-Service Calculator'!$G$1:$G$10000="7-part call")+('3-Service Calculator'!$G$1:$G$10000="8-high part")),--('3-Service Calculator'!$X$1:$X$10000=1),'3-Service Calculator'!$S$1:$S$10000)/SUMPRODUCT((('3-Service Calculator'!$G$1:$G$10000="7-part call")+('3-Service Calculator'!$G$1:$G$10000="8-high part")),--('3-Service Calculator'!$X$1:$X$10000=1)))/24,0)

Also note, that SUMPRODUCT is less efficient than AVERAGEIFS so try to limit the range sizes to the absolute largest you will need... a range of 1:10000 may cause performance issues if you have many of them.

Edit: You can possibly also just add 5 different AVERAGEIFS together, one for each Grange value (keeping the X and S ranges in tact in each). and then divide the entire thing by 24