countif twice

  • Thread starter Thread starter kevinherring
  • Start date Start date
K

kevinherring

Hi all

lets say I have two columns of data. one containing a date and one
containing a supplier.

I want to be able to count the number of entries that happen for a
given supplier in a given month.

I could used countif() if i was just after one criteria, but i'm not,
so i cant.


any ideas?

thanks

Kevin
 
kevinherring said:
Hi all

lets say I have two columns of data. one containing a date and one
containing a supplier.

I want to be able to count the number of entries that happen for a
given supplier in a given month.

I could used countif() if i was just after one criteria, but i'm not,
so i cant.
Click to expand...

Use something along these lines:
=SUMPRODUCT((A1:A100=xxx)*(B1:B100=yyy))

xxx and yyy can be entered directly into the formula, or replaced by
references to cells containing the values.
For a particular month, you need to extract that month from the date. If all
dates are within a year, you can do this with the MONTH function:
=SUMPRODUCT((MONTH(A1:A100)=xxx)*(B1:B100=yyy))
If not, it's probably easier to have >= and <= conditions.
For example,:
=SUMPRODUCT((A1:A100>=DATE(2003,7,1))*(A1:A100<=DATE(2003,7,31))*(B1:B100="s
upplierA"))
will count the number of entries for "SupplierA" during July 2003.
 
=SUMPRODUCT((A2:A1600>=A2)*(B2:B1600="Supplier"))-SUMPRODUCT((A2:A1600>A10)*
(B2:B1600="Supplier"))

Where A2:A1600 are the dates and B2:B1600 are the Supplier. A2 is the
earliest date and A10 is the latest date
 
excellent thanks for your help, quick query though, i have completely
forgotten. how do i convert a text month i.e. "january" into a number
i.e. 1?

thanks

kevin
 
'salright i got it:

=MONTH(DATEVALUE("1/"&B1&"/2003"))

anyone think of a better way
 
There are other ways but I see nothing wrong with your way
It can be shortened though

=MONTH(--("1/"&B1&"/2003"))
 
You must have your set up as January, February, etc., so
=sumproduct((a2:a200="January")*(b2:b200="Joe"))
 
Back
Top