Countif or sumproduct using a date range and another criteria

[michaelberrier]

I've been through about two dozen threads but cannot find the specific
solution I need, so here goes.

I need to count how many times a particular occurence (we'll say
"Violation" and it is listed in range B4:B400) occurred in January of
last year (listed in range J4:J400) I've tried putting two Sumproduct
criteria together and I've tried modified countifs, but neither will
work with both criteria in my current form.

I appreicate any help.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(B4:B400="Violation"),--(MONTH(J4:J400)=1),--(YEAR(J4:J400)=2008))

 

[michaelberrier]

Try this:

=SUMPRODUCT(--(B4:B400="Violation"),--(MONTH(J4:J400)=1),--(YEAR(J4:J400)=2­008))

--
Biff
Microsoft Excel MVP








- Show quoted text -

Absolutely perfect! Thanks.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try this:

=SUMPRODUCT(--(B4:B400="Violation"),--(MONTH(J4:J400)=1),--(YEAR(J4:J400)=2­008))

--
Biff
Microsoft Excel MVP








- Show quoted text -

Absolutely perfect! Thanks.

 

[lazza]

Wow

I too have looked everywhere for this exact formula, THANKS SO MUCH! I hadn't thought it might be something as simple as that, I was trying to sum the occurences using a date range by specifying the end and start dates.

You have saved me a lot of headache!