How can I use COUNTIF to sum for two conditions?
 

I understand i have to use an array formula, but I don't know how!

I have an array listing dates when products arrived from diffferent
countries. I want to produce a chart which shows the number of arrivals for
each month from each country. COUNTIF will produce a total of arrivals for
each month, or a total from each country (ie for one condition) - but how
about for two conditions - this country in this month?

=SUMPRODUCT(--(TEXT(A1:A100,"mmm")="Jan"),--(B1:B100="Mexico"))

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff" wrote in message
...
I understand i have to use an array formula, but I don't know how!

I have an array listing dates when products arrived from diffferent
countries. I want to produce a chart which shows the number of arrivals
for
each month from each country. COUNTIF will produce a total of arrivals
for
each month, or a total from each country (ie for one condition) - but how
about for two conditions - this country in this month?

If you mean a particu;ar month that you select (assume december 2004)

=SUMPRODUCT(--(MONTH(A2:A100)=12),--(YEAR(A2:A100)=2004),--(B2:B100="this_country"))

or if you mean the present month according to your computers clock

=SUMPRODUCT(--(MONTH(A2:A100)=MONTH(TODAY())),--(YEAR(A2:A100)=YEAR(TODAY())),--(B2:B100="this_country"))


Regards,

Peo Sjoblom

"Jeff" wrote:

I understand i have to use an array formula, but I don't know how!

I have an array listing dates when products arrived from diffferent
countries. I want to produce a chart which shows the number of arrivals for
each month from each country. COUNTIF will produce a total of arrivals for
each month, or a total from each country (ie for one condition) - but how
about for two conditions - this country in this month?

Thanks Bob

Trouble is the data are extracted from a data base and one record represents
an item - includes date received and country of origin. List is in the order
of 3000 records, and i want a summary chart with dates (all 1st of month in
'Jan-03' format) down left side with list of countries (as column header)
across top. the number in each cell shoul equal the number of items received
from that country in that month. Summary should change if country name is
changed.

Jeff

"Bob Phillips" wrote:

=SUMPRODUCT(--(TEXT(A1:A100,"mmm")="Jan"),--(B1:B100="Mexico"))

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff" wrote in message
...
I understand i have to use an array formula, but I don't know how!

I have an array listing dates when products arrived from diffferent
countries. I want to produce a chart which shows the number of arrivals
for
each month from each country. COUNTIF will produce a total of arrivals
for
each month, or a total from each country (ie for one condition) - but how
about for two conditions - this country in this month?

thanks Peo - can you see my reply to Bob above?

"Peo Sjoblom" wrote:

If you mean a particu;ar month that you select (assume december 2004)

=SUMPRODUCT(--(MONTH(A2:A100)=12),--(YEAR(A2:A100)=2004),--(B2:B100="this_country"))

or if you mean the present month according to your computers clock

=SUMPRODUCT(--(MONTH(A2:A100)=MONTH(TODAY())),--(YEAR(A2:A100)=YEAR(TODAY())),--(B2:B100="this_country"))


Regards,

Peo Sjoblom

"Jeff" wrote:

I understand i have to use an array formula, but I don't know how!

I have an array listing dates when products arrived from diffferent
countries. I want to produce a chart which shows the number of arrivals for
each month from each country. COUNTIF will produce a total of arrivals for
each month, or a total from each country (ie for one condition) - but how
about for two conditions - this country in this month?