formulas
 

I am attempting to get results from the following spreadsheet:

Column D Column N
Product No. Date Sold
11 4/15/04
11 5/15/04
11 9/30/04
18 2/22/04
18 7/4/04
18 8/28/04

I have the total number of product no. 11 sold. From the above data I need
to find out how many of Product No. 11 was sold prior to 5/25/04 and how many
were sold after 5/25/04. I've tried COUNTIF, SUM(IF), COUNT(IF), SUMPRODUCT
as array formulas and not. Any assistance would be appreciated.

formulas
 

=SUMPRODUT(--(D2:D200=11),--(N2:N200<=--"2006-04-25"))

for prior to 25th April, for after (you will have to decide where the =
goes.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terri" wrote in message
...
I am attempting to get results from the following spreadsheet:

Column D Column N
Product No. Date Sold
11 4/15/04
11 5/15/04
11 9/30/04
18 2/22/04
18 7/4/04
18 8/28/04

I have the total number of product no. 11 sold. From the above data I
need
to find out how many of Product No. 11 was sold prior to 5/25/04 and how
many
were sold after 5/25/04. I've tried COUNTIF, SUM(IF), COUNT(IF),
SUMPRODUCT
as array formulas and not. Any assistance would be appreciated.

formulas
 

try sumif where b1 has your date
=sumif(b2:b22,"<"&b1,a2:a22)


--
Don Guillett
SalesAid Software

"Terri" wrote in message
...
I am attempting to get results from the following spreadsheet:

Column D Column N
Product No. Date Sold
11 4/15/04
11 5/15/04
11 9/30/04
18 2/22/04
18 7/4/04
18 8/28/04

I have the total number of product no. 11 sold. From the above data I
need
to find out how many of Product No. 11 was sold prior to 5/25/04 and how
many
were sold after 5/25/04. I've tried COUNTIF, SUM(IF), COUNT(IF),
SUMPRODUCT
as array formulas and not. Any assistance would be appreciated.

formulas
 

Hi Terri

One way
=SUMPRODUCT(--($D$2:$D$100=11),--($N$2:$N$100<DATE(2004,5,25))
This assumes the data in column D is numeric. If it is text, then wrap
the 11 in quotes "11".
Change the operator to <= if you want to also include 5/25/04.
Repeat the procedure with operator changed to to find answer for after
5/25/04.
Change size of ranges to suit, but keep them of equal length.

--
Regards

Roger Govier


"Terri" wrote in message
...
I am attempting to get results from the following spreadsheet:

Column D Column N
Product No. Date Sold
11 4/15/04
11 5/15/04
11 9/30/04
18 2/22/04
18 7/4/04
18 8/28/04

I have the total number of product no. 11 sold. From the above data I
need
to find out how many of Product No. 11 was sold prior to 5/25/04 and
how many
were sold after 5/25/04. I've tried COUNTIF, SUM(IF), COUNT(IF),
SUMPRODUCT
as array formulas and not. Any assistance would be appreciated.

formulas
 

Thanks for replying - all information was helpful. My report is complete now.
Thanks again.
"Bob Phillips" wrote:

=SUMPRODUT(--(D2:D200=11),--(N2:N200<=--"2006-04-25"))

for prior to 25th April, for after (you will have to decide where the =
goes.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terri" wrote in message
...
I am attempting to get results from the following spreadsheet:

Column D Column N
Product No. Date Sold
11 4/15/04
11 5/15/04
11 9/30/04
18 2/22/04
18 7/4/04
18 8/28/04

I have the total number of product no. 11 sold. From the above data I
need
to find out how many of Product No. 11 was sold prior to 5/25/04 and how
many
were sold after 5/25/04. I've tried COUNTIF, SUM(IF), COUNT(IF),
SUMPRODUCT
as array formulas and not. Any assistance would be appreciated.