Sumproduct

  • Thread starter Thread starter Paul Marsh
  • Start date Start date
P

Paul Marsh

I need to be able to count data that matches criteria from a number o
columns and am using SUMPRODUCT to do this effectively. However one o
the columns contains dates in the dd/mm/yyyy format and I need to b
able to differentiate between month and year in my criteria. Fo
example I need to be able to count occurances of Jan 03 as differen
from Jan 04. Does anyone know how I do this?

Regards

Pau
 
Hi Paul!

Here's one way:

=SUMPRODUCT(--(MONTH($A$1:$A$11)=1),--(YEAR($A$1:$A$11)=2003),($B$1:$B$11))

Rather than hard coding in the month and year numbers you might use cell
references and thereby create a table.

But if you're thinking of that, you would probably be best off using a Pivot
Table
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

Thanks for the quick reply I will give that a go. I haven't reall
used pivot tables but it looks like I'm going to be learning quit
soon.

Cheers

Pau
 
Back
Top