Help - Looking for a Sumproduct formula

[don_maestro_33]

Hello Friends - I am searching for a formula that will count how many
quotes and orders Jane is processing on a daily basis for a specific
region. The spreadsheet I am working on has two sheets:

Sheet one looks like this:
A B C D
Date Region Quote/Order Name
10/02/06 East Q Jane
10/02/06 West O Jane
10/02/06 West O Bill
10/02/06 West O Jane
10/02/06 West Q Jane
10/03/06 East O Bill


Sheet two looks like this:


Monday (10/02/06)

A B C D
Name Quote Orders Region
Jane 1 2 West
Bill 1 0 West


Is there a sumproduct formula that will calculate the amount of quotes
and orders Jane has processed for the west region on 10/02/06 in sheet
one onto column B and C of the second sheet? My guess is I am going to
need two formula's: one for column B (Quotes) and one for column C
(Orders).

In this example Jane processed 1 quote and 2 orders for the west region
on 10/02/06.


Any assistance is truly appreciated.


Regards,
Joe

 

[Domenic]

Try PivotTables. Otherwise, by formula...

Assumptions:

A2:C7 contains the source data

F1 contains the data of interest

F3:F4 contains Jane and Bill

I3:I4 contains West

Formulas:

G3, copied down:

=SUMPRODUCT(--($A$2:$A$7=$F$1),--($B$2:$B$7=$I3),--($C$2:$C$7="Q"),--($D$
2:$D$7=$F3))

H3, copied down:

=SUMPRODUCT(--($A$2:$A$7=$F$1),--($B$2:$B$7=$I3),--($C$2:$C$7="O"),--($D$
2:$D$7=$F3))

Hope this helps!

 

[Max]

One way to use only a single start formula is to set it up read the leftmost
character off the col labels in Sheet2, something like this ..

Assume source data in Sheet1 cols A to D, within row2 to 100
(real dates assumed in col A)

In Sheet2,

Assume A1 houses a real date, say: 10/02/06
while A22 houses the labels:

Name Quote Orders Region

Cols A and D are presumed filled with names and regions

Place this in B3:
=SUMPRODUCT((Sheet1!$A$2:$A$100=$A$1)*(Sheet1!$B$2:$B$100=$D3)*(Sheet1!$C$2:$C$100=LEFT(B$2))*(Sheet1!$D$2:$D$100=$A3))
Copy B3 to C3, fill down as far as required

 

[Max]

One way to use only a single start formula is to set it up read the leftmost
character off the col labels in Sheet2, something like this ..

Assume source data in Sheet1 cols A to D, within row2 to 100
(real dates assumed in col A)

In Sheet2,

Assume A1 houses a real date, say: 10/02/06
while A22 houses the labels:

Name Quote Orders Region

Cols A and D are presumed filled with names and regions

Place this in B3
=SUMPRODUCT((Sheet1!$A$2:$A$100=$A$1)*(Sheet1!$B$2:$B$100=$D3)*(Sheet1!$C$2:$C$100=LEFT(B$2))*(Sheet1!$D$2:$D$100=$A3))
Copy B3 to C3, fill down as far as required

 

[Carim]

Hi Joe,

Provided you input your date in sheet2 cell E1,
then in sheet2 cell B2 formula is :

=SUMPRODUCT(--(Sheet1!$D$2:$D$7=Sheet2!$A2)*--(Sheet1!$C$2:$C$7=LEFT(B$1,1))*--(Sheet1!$B$2:$B$7=Sheet2!$D2)*--(Sheet1!$A$2:$A$7=Sheet2!$E$1))

HTH
Carim