KC!!!! It worked!!!! I can't possibly imagine how "sumproduct" does this, so
can you explain the logic? That would really help me.

You have saved me untold work creating "helper"columns and pivot tables.
THANK YOU!!!

"KC" wrote:

Did you try the formulas I provided? I think it does what you want.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"MCC" wrote:

Hi KC - no I'm not trying to multiply anything. There are 2 conditions that
need to be med - when both conditions are met, then I want excel to count the
number of times the number "2" appears. So, in the example, there are 5 times
that USC and 2008-10 match the criteria and there are a total of four "2"s.
The problem I found was that as soon as a row had LAR 2008-10 (or it could
just as easily have been USC 2008-11), the value returned was "no". I want
to count all the number twos in the five "classes" each time my 2 conditions
are met. Does that help?

"KC Rippstein" wrote:

I'm not quite following what you're after, but I would think SUMPRODUCT
should do the trick.

If you use the following formula, it returns a single answer of 4 using your
data set:
=SUMPRODUCT((Mapped!M2:M12="USC")*(Mapped!B2:B12=" 2008-10")*(Mapped!AC2:AG12=2))

Where you lost me is that you're then copying the formula down to other
rows. If you need the answer per row (so an answer of 1 for row 2, 0 for row
3, 1 for row 4, 0 for 5 & 6, and 1 for 7 & 8), which ultimately totals the
answer of 4 given in the first formula above, then this formula at the end of
each row should work:
=SUMPRODUCT((Mapped!M2="USC")*(Mapped!B2="2008-10")*(Mapped!AC2:AG2=2))

"MCC" wrote:

I have a data set which has yyyy-mm, region, and then data entered in one of
5 columns. If yyyy-mm = a set month and region = a specific region, then I
want to count all the times the number two appears in the next set of
columns.

Here's the formula I have:
{=IF( AND(Mapped!M2:M12="USC", Mapped!B2:B12="2008-10"),
COUNTIF(Mapped!AC2:AG12,2),"no")}

Here's an example of the data:
YYYY-MM Region class1 class2 class 3 class 4 class 5
2008-10 USC 6 2 7 0
2008-10 USC 5 1
2008-10 USC 3 2 7 0 0
2008-10 LAR 4 2 6 9
2008-10 LAR 9 2 7 9 0
2008-10 USC 1 2 5 6
2008-10 USC 4 2 7 0 0

The formula works fine down 3 rows. The minute the region chnages to LAR,
the formula returns "no" or false. How can I get a formula to count all
values of 2 in any of columns 3-7 for which date=2008-10 and region=USC ?