I have to calculate the sum of sales for date ranges, region, contract
status. I'm thinking this will take two basic statements (Count &
sumproduct) that would have to be changed for each result cell. My problem
(if I'm on t he right track at all) is the syntax of nesting all the
conditional functions. If there is an easier way, suggestions are greatly
appreciated.

Thanks

Data data would be:
Region Date Sales $ Contract
111 11/01/04 100 yes
111 11/12/04 200 no
111 11/04/04 300 yes
124 11/07/04 400 no
124 11/13/04 500 yes
135 11/05/04 600 no

Result should be
For Period 11/01-11/07
Number Sale w/ Contract Sale w/o Contract
111 2 400
124 1 400
135 1 600

For Period 11/08-11/14
111 1 200
124 1 500
135

I have a similar sheet set up to return orders received by month. The
array (CTRL+SHIFT+ENTER) formula for January looks like this:

{=SUM(IF(Master!$F$4:$F$3972=$A276,IF(Master!$D$4: $D$3972=DATEVALUE("1/1/2004"),IF(Master!$D$4:$D$3972<DATEVALUE("2/1/2004"),Master!$H$4:$H$3972,0),0)))}.

"Master" is the main sheet into which all incoming orders are entered.
The first range is the column to look in for the comparison. =$A276
means only lookup those items in Master that match cell $a276 (the part
number in my case) and then only those values = Jan.1 or <= Feb.2. If
all this is true, then add up the values in Master Column H (the sales
values). Otherwise, return 0.

Hope this helps.


--
wmjenner


------------------------------------------------------------------------
wmjenner's Profile: http://www.excelforum.com/member.php...fo&userid=5282
View this thread: http://www.excelforum.com/showthread...hreadid=319909

Number:

=SUMPRODUCT(--(A1:A100=111),--(B1:B100=--("2004/11/02)),--(B1:B100<=--("200
4/11/07")))

Sales

=SUMPRODUCT(--(A1:A100=111),--(B1:B100=--("2004/11/02)),--(B1:B100<=--("200
4/11/07")),C1:C100)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"wal50" wrote in message
...
I have to calculate the sum of sales for date ranges, region, contract
status. I'm thinking this will take two basic statements (Count &
sumproduct) that would have to be changed for each result cell. My
problem
(if I'm on t he right track at all) is the syntax of nesting all the
conditional functions. If there is an easier way, suggestions are greatly
appreciated.

Thanks

Data data would be:
Region Date Sales $ Contract
111 11/01/04 100 yes
111 11/12/04 200 no
111 11/04/04 300 yes
124 11/07/04 400 no
124 11/13/04 500 yes
135 11/05/04 600 no

Result should be
For Period 11/01-11/07
Number Sale w/ Contract Sale w/o Contract
111 2 400
124 1 400
135 1 600

For Period 11/08-11/14
111 1 200
124 1 500
135

wal50,

if the data is in the range A1:D7 and the output table looks like follows
and is in the range F1:I5


F G H I

-------------------------------------------------------------

1| For Period 11/01/04 11/07/04

2| # yes no

3| 111 2 400 0

4| 124 1 0 400

5| 135 1 0 600



then insert into G3 the following formula and copy/paste it down :
=SUMPRODUCT(($A$2:$A$7=$F3)*($B$2:$B$7=$H$1)*($B$ 2:$B$7<=$I$1))

and this one into H3 and then copy/paste it down and to the right:
=SUMPRODUCT(($A$2:$A$7=$F3)*($B$2:$B$7=$H$1)*($B$ 2:$B$7<=$I$1)*($D$2:$D$7=H$2)*$C$2:$C$7)

Regards,
KL


"wal50" wrote in message
...
I have to calculate the sum of sales for date ranges, region, contract
status. I'm thinking this will take two basic statements (Count &
sumproduct) that would have to be changed for each result cell. My
problem
(if I'm on t he right track at all) is the syntax of nesting all the
conditional functions. If there is an easier way, suggestions are greatly
appreciated.

Thanks

Data data would be:
Region Date Sales $ Contract
111 11/01/04 100 yes
111 11/12/04 200 no
111 11/04/04 300 yes
124 11/07/04 400 no
124 11/13/04 500 yes
135 11/05/04 600 no

Result should be
For Period 11/01-11/07
Number Sale w/ Contract Sale w/o Contract
111 2 400
124 1 400
135 1 600

For Period 11/08-11/14
111 1 200
124 1 500
135