Count Issues

[rileym]

I have two columns, Date and Location. In location, there are
different possibilities, lets say Alpha, Bravo, Charlie, Delta. Here i
a small example of my data.

_Date--------------Location_
1/2/06------------Alpha
2/2/06------------Bravo
2/4/06------------Charlie

In reality, I have numerous dates with these four locations. I a
trying to track the total number of times a particular location is use
in a month.
I.E. In March of 2006, Location Charlie occurred 9 times.

I have figured out how to get total times Charlie was used w/
=COUNTIF(location, "Charlie")
and how many locations were used for a month w/
=COUNTIF(date,">"&B10)-COUNTIF(date,">"&B11)

I just don't know how to join these two together to get Locatio
Charlie for March 06. Would I use COUNT with AND?

Thanks in Advanc

 

[RagDyer]

Try this:

=SUMPRODUCT((A1:A50>=--"3/1/06")*(A1:A50<=--"3/31/06")*(B1:B50="Charlie"))

You could, of course, assign individual cells to contain your variable
criteria, so that you wouldn't have to revise the formula to change your
conditions.

For example:
C1 = start date
C2 = end date
C3 = location

=SUMPRODUCT((A1:A50>=C1)*(A1:A50<=C2)*(B1:B50=C3))

 

[rileym]

RagDyer

Thank you for the response. I think I may have ommitted some pertinen
information, as the formula you provided doesn't seem to work for me.
1) For the Dates column, I have assigned all of Column A so that I ca
type in "date" into my formula.
2) For the location column, I have assigned all of Column B so that
can type in "location" into my formula.

I did this because the Date and Location columns are on one worksheet
while where I am attempting to have the information on anothe
worksheet.

Thanks agai

 

[Dave Peterson]

This kind of formula (=sumproduct()) can't use the whole column.

Maybe you can redefine the ranges so that they're large enough to cover the
worst case scenario. If you think you'll use 2000 rows, then double it and add
a few more (make it 10,000 rows deep).

Or maybe you can use a dynamic range that grows/contracts with your data.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic