ARRAY with countif

[MCC]

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 ?

 

[KC Rippstein hotmail com>]

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]

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 hotmail com>]

Did you try the formulas I provided? I think it does what you want.

 

[MCC]

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 hotmail com>]

Countif and Sumif are limited to testing against just one condition, unless
you use array formulas, which are resource hogs. DSUM is good but requires
you to setup a separate table somewhere. Sumproduct is a way to run a count
or sum meeting multiple criteria without a resource-hungry array and without
manually setting up a secret table somewhere. It's an invaluable asset to
Excel.

When you want it to sum, you indicate the range to sum at the end of the
formula...when you want it to count, you don't add the sum range at the end.

So if I didn't want the Count of instances of "2" but instead wanted the
Sum, I would've added the range to be summed at the end (which would give me
an answer of 8)
=SUMPRODUCT((Mapped!M2:M12="USC")*(Mapped!B2:B12="2008-10")*(Mapped!AC2:AG12=2)*(Mapped!AC2:AG12))

The formula essentially creates virtual tables to run each test. So for the
first test it goes down M2:M12 and creates a TRUE or FALSE answer for each.
Right next to that table it sets up another virtual column of answers for the
B2:B12 test with more T/F answers, then AC2:AC12 is the next column in the
virtual table with virtual T/F answers, etc. Once all the tests are run and
the virtual table is complete, it goes across the first row of that virtual
table and multiplies the answers...T * T = 1, but multiplying by an F results
in 0.

So your virtual table created by the sumproduct formula is:
Row 2:
TRUE (M2) *TRUE (B2) *FALSE (AC2) = 0 (for the sum version, it takes the 1
or 0 and multiplies by the value you wanted to pull)
T*T*T (AD2) = 1 (so the sum version would be 1*2=2
T*T*F (AE2) = 0
and so on, then it goes to Row 3:
T (M3) *T (B3) *F (AC3) = 0
and so on until the table is done. Then you get either the total count (4)
or the total sum (8).

The rules you have to follow are pretty easy:
1) The height of the range must be consistent for each test. So the second
test (B2:B12) could have been B13:B23 if that's the appropriate testing range
because it remains 11 rows tall. B13 would be used alongside the M2 and AC2
tests, B14 would correspond to M3 and AC3, etc. This works well when you
have multiple sheets and stuff is not necessarily on the same rows from sheet
to sheet.
2) TRUE is treated as the number 1 by Excel, and FALSE as 0. However, some
people by habit put a double-negative (--) in front of all tests just to be
sure that the T & F results are indeed treated as 1s and 0s. So it would
have looked like this
=SUMPRODUCT(--(Mapped!M2:M12="USC")*--(Mapped!B2:B12="2008-10")*--(Mapped!AC2:AG12=2))
3) You can use either the asterisk or a comma in the sumproduct formula. So
it could have been set up like this as well
=SUMPRODUCT((Mapped!M2:M12="USC"),(Mapped!B2:B12="2008-10"),(Mapped!AC2:AG12=2))

Hope that helps!

 

[MCC]

Exellent - excel is so powerful. I have people at work that keep telling me
to go use JMP, but excel is so much easier to use and follow and, I think, is
more flexible. I keep resisting (and I do know both and prefer excel). There
is so much more to excel like in this example that makes it really
great...but can be hard to find on your own. Thank you for your time and
willingness to help.