Multiple SumProduct conditions

[wal50]

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

 

[wmjenner]

I have a similar sheet set up to return orders received by month. Th
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. =$A27
means only lookup those items in Master that match cell $a276 (the par
number in my case) and then only those values >= Jan.1 or <= Feb.2. I
all this is true, then add up the values in Master Column H (the sale
values). Otherwise, return 0.

Hope this helps

 

[Bob Phillips]

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)

 

[KL]

wal50,

if the data is in the range A17 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