# Multiple Criteria's

1. ## 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"

``Please Login or Register  to view this content.``
Criteria =

G:G= "4-more time"
G:G= "3-recall"

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

2. ## Re: Multiple Criteria's

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....

3. ## Re: Multiple Criteria's

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.``

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

4. ## Re: Multiple Criteria's

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

5. ## Re: Multiple Criteria's

Originally Posted by NBVC
That criteria looks like the one in the formula already. Is that the right one?
I adjusted it. The formula has 7-part call, the addition is 8-high part.

6. ## Re: Multiple Criteria's

Try:

``Please Login or Register  to view this content.``

7. ## Re: Multiple Criteria's

Originally Posted by NBVC
Try:

``Please Login or Register  to view this content.``
Thank you, that worked perfectly. Currently i am looking into the 2nd formula you provided me. Seems to have given me a lesser SUM than the original formula; BRB soon.

8. ## Re: Multiple Criteria's

``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``

The formula you created is a little over my head, so I will explain the one that I have. Its reads as follows…
Criteria
If G:G = 3-service call < plus the additions > 3-recall, 4-more time, 6-follow up
And
X:X = 1
Result
Go to
S:S = SUM the average/24

9. ## Re: Multiple Criteria's

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.

10. ## Re: Multiple Criteria's

``Please Login or Register  to view this content.``

Criteria = '3-Service Calculator'!\$G:\$G,"8-high part"

11. ## Re: Multiple Criteria's

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1