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 A2:D2 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))*(Sheet 1!$D$2:$D$100=$A3))
Copy B3 to C3, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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