Basic: Looking up Data in Range

[JonZ]

This is probably basic stuff.

I presently use COUNTIF to count the number of instances of the letter
"x" or "o" in a column:
=COUNTIF(B:B,"x")

I need to find totals of one condition in a column when another column
has another condition.

In the following data range, for example, I might need to find the
number of times "x" appears in column B during a particular month, like
February, or during a certain time of day.

A B C
January 4 x morning
January 4 o afternoon
January 12 o morning
January 15 o morning
January 15 x afternoon
February 2 x morning
February 2 x morning
Ferbrary 4 o morning
February 10 x morning

I might also like to count, for example, the number of times "x"
apppears in the morning in February.

Please write out examples. My date format is MM-DD-YY.

 

[Jason Morin]

=SUMPRODUCT((TEXT(A1:A9,"mmm")="Jan")*(B1:B9="x"))

Count all instance of "x" where month = January.

HTH
Jason
Atlanta, GA

 

[Guest]

=SUMPRODUCT(--(MONTH(A2:A200)=1),--(B2:B200="x"),--(C2:C200="morning"))


Regards,

Peo Sjoblom

 

[JonZ]

According to my copy of EXCEL 2000, SUMPRODUCT is a math function and
returns sums of multiplications

SUMPRODUCT
Multiplies corresponding components in the given arrays, and returns the
sum of those products.

It would not work in doing what I want to do. You must have
misunderstood my question.

 

[JonZ]

According to my copy of EXCEL 2000, SUMPRODUCT is a math function and returns
sums of multiplications

SUMPRODUCT
Multiplies corresponding components in the given arrays, and returns the sum
of those products.

It would not work in doing what I want to do. You must have misunderstood my
question.

 

[Jason Morin]

No, I didn't misread your question. SUMPRODUCT is a
powerful alternative to array formulas as far as summing
and counting when multiple criterias are needed. But you
are right...it is simply a math function. To understand
what I'm talking about, read:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Jason

 

[Dave Peterson]

Just wondering if you tried the suggestions before posting back that it wouldn't
work???

 

[Peo Sjoblom]

So, you post a question, you get 2 similar answers, whereupon you disregard
both answers without trying the solutions,
pasting from excel help? Amazing!

Here's some info on SUMPRODUCT


http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[JonZ]

That explains it.
I need to figure out how to extract the month from the date field. My
date field contains a day, month and year. When I click on a date field
the format is: 1/12/2004.