Countif for multiple criteria
 

Hi

The spreadsheet im using has date values in Column D and country names
in column I and im am trying to count rows of entries for each country
name entered each month.

The criteria example for March is....

Count column I = Denmark where Column D is between 01/03/2005 and
31/03/2005 inclusive

Any help would be appreciated

Thanks....Jim


--
Jim Rennie
------------------------------------------------------------------------
Jim Rennie's Profile: http://www.excelforum.com/member.php...o&userid=24461
View this thread: http://www.excelforum.com/showthread...hreadid=380571

try
=sumproduct(--(I1:I1000="Denmark"),--(D1:D1000datevalue("01/03/2005"),--(D1:D1000<datevalue("31/03/2005"))

Note depending on your version of excel, you may have to play with the
format of the dates.
I normally prefoer to put the dates outside the Sumproduct and reference them.

"Jim Rennie" wrote:


Hi

The spreadsheet im using has date values in Column D and country names
in column I and im am trying to count rows of entries for each country
name entered each month.

The criteria example for March is....

Count column I = Denmark where Column D is between 01/03/2005 and
31/03/2005 inclusive

Any help would be appreciated

Thanks....Jim


--
Jim Rennie
------------------------------------------------------------------------
Jim Rennie's Profile: http://www.excelforum.com/member.php...o&userid=24461
View this thread: http://www.excelforum.com/showthread...hreadid=380571

Jim Rennie wrote:
Hi

The spreadsheet im using has date values in Column D and country names
in column I and im am trying to count rows of entries for each country
name entered each month.

The criteria example for March is....

Count column I = Denmark where Column D is between 01/03/2005 and
31/03/2005 inclusive

Any help would be appreciated

Thanks....Jim



K2: Denmark
L2: 01/03/2005

with dd/mm/yyyy as format. Note that the formula below requires that in
L the criteria dates are set to the first day date of the month/year
combo's of interest.

M2:

=SUMPRODUCT(($D$2:$D$500-DAY($D$2:$D$500)+1=L2)+0,($I$2:$I$500=K2)+0)

Another option is to create an additional column, say, E using...

E2, copied down:

=D2-DAY(D2)+1&"#"&I2

then invoke with K2 and L2 as specified above:

M2:

=COUNTIF($E$2:$E$500,L2&"#"&K2)

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

If you're worried about the format for dates, you can use:

=sumproduct(--(I1:I1000="Denmark"),--(D1:D1000date(2005,03,01)),
--(D1:D1000<date(2005,03,31))

(and didn't you drop a closing paren from that middle portion?)

And another option:

=sumproduct(--(I1:I1000="Denmark"),--(text(D1:D1000,"yyyymm")="200501"))

But that will suffer from yyyymm. The OP will have to use whatever that
language uses for yyyy and mm.



bj wrote:

try
=sumproduct(--(I1:I1000="Denmark"),--(D1:D1000datevalue("01/03/2005"),--(D1:D1000<datevalue("31/03/2005"))

Note depending on your version of excel, you may have to play with the
format of the dates.
I normally prefoer to put the dates outside the Sumproduct and reference them.

"Jim Rennie" wrote:


Hi

The spreadsheet im using has date values in Column D and country names
in column I and im am trying to count rows of entries for each country
name entered each month.

The criteria example for March is....

Count column I = Denmark where Column D is between 01/03/2005 and
31/03/2005 inclusive

Any help would be appreciated

Thanks....Jim


--
Jim Rennie
------------------------------------------------------------------------
Jim Rennie's Profile: http://www.excelforum.com/member.php...o&userid=24461
View this thread: http://www.excelforum.com/showthread...hreadid=380571



--

Dave Peterson