Counting occurences on multiple criteria

[Ron H]

I have a sheet of business data.
There are 3 locations of service and I want to count the numbers o
sales in each location over a given time period.

Column C has the dates of sales
Column H has the location

I tried to get count for sales for the current month of June 2004 a
location A

=SUMPRODUCT((C5:C309>"5/31/2004")*(H5:H309="A"))

and I get 0 (which is wrong.)

I took out the quotes around the date value

=SUMPRODUCT((C5:C309>5/31/2004)*(H5:H309="A"))

and I get a number which is much too high and is obviously incorrect.

What am I doing wrong

 

[Bob Phillips]

Try this

=SUMPRODUCT((C5:C309>(--("2004/05/31")))*(H5:H309="A"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron H]

Thanks for your solution. I suppose it has something to do wit
SUMPRODUCT seeing the date as text.

By the way, seeing that I needed a number instead of text,I tried th
following which also worked:

=SUMPRODUCT((C5:C309>(DATEVALUE("05/31/2004")))*(H5:H309="A"))

With regards to my initial formula not working why is it tha
SUMPRODUCT sees (C5:C309>"5/31/2004") as not being a Boolean result a
a numerical 0 or 1?

Ron Hekier

 

[Bob Phillips]

DATEVALUE works as well, I just prefer --("2004/05/31") as I know it works
in all international settings (MS gets in a real pickle with dates), and I
guess that it is quicker as well using unary operators rather than a
function.

SUMPRODUCT sees (C5:C309>"5/31/2004") does see them all as Boolean results,
they just all failed so you got zero. Comparing a date against a text
string will return False.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)