Multiple Criteria's

Hello guys-

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

Please Login or Register  to view this content.

Criteria =

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

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!

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

the definition of Average is to SUM Divided by the Count

So the first SUMPRODUCT() sums, then the second SUMPRODUCT() counts.

The + signs between the first 5 conditions means OR.. so what it does is check if the G range contains any of those 5 conditions and returns TRUE if they do. Then it checks the X range for 1 and returns TRUE, the 2 results are matched up and multiplied.. any TRUE*TRUE returns 1 and any other combination yields 0 result, and those 1's and 0's are subsequently multiplied by the corresponding values in S range and added together to give total SUM of S range where G range meets the criterias at the same time that X meets the criteria set.

The second Sumproduct() is the same, except there is no S range to sum and so it just returns a count of the matching condition combinations.

So the Sum divided by the count should equate to the AVERAGE. Then that average is divided by 24.

Try:

Please Login or Register  to view this content.

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