PDA

View Full Version : Solved: Formula Problems

john3j
06-15-2009, 09:33 AM
Hello all. I am trying to write a formula like a previous one:

=SUMPRODUCT(--('Test Data'!Z2:Z787="Basic"),--('Test Data'!G2:G787="09"))

Here is the problem, now I need to count if there are four different names in column Z. Even if I could have it exclude one or two specific ones. Lets say for instance that I needed it to count the following conditions:

1. Column z = "Basic" and column g = "09"
2. Column z = "Hard" and column g = "09"
3. Column z = "Silly" and column g = "09"
4. Column z = "Impossible" and column g = "09"

How can I include all of these and exclude all others?:dunno

xld
06-15-2009, 10:37 AM
=SUMPRODUCT(('Test Data'!Z2:Z787={"Basic","Hard","Silly","Impossible"})
*('Test Data'!G2:G787="09"))

john3j
06-15-2009, 08:49 PM
Awesome that works perfectly. How would I add an instance that counts if range c2:c1000 ="yes" in addition to the previous criteria?

xld
06-16-2009, 12:26 AM
Just append that condition

=SUMPRODUCT(('Test Data'!Z2:Z787={"Basic","Hard","Silly","Impossible"})
*('Test Data'!G2:G787="09")
*('Test Data'!C2:C787="yes"))

Note that I have used C2:C787 not C2:C1000 as the ranges must be consistent in size