Countif for multiple criteria

[Jim Rennie]

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

 

[Guest]

try
=sumproduct(--(I1:I1000="Denmark"),--(D11000>datevalue("01/03/2005"),--(D11000<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.

 

[Aladin Akyurek]

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.

 

[Dave Peterson]

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

=sumproduct(--(I1:I1000="Denmark"),--(D11000>date(2005,03,01)),
--(D11000<date(2005,03,31))

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

And another option:

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

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