Sumproduct - Blanks
 

=SUMPRODUCT(--('DETAIL 11-04 CTRTS'!$A$4:$A$842=DATE(2004,11,2))*('DETAIL
11-04 CTRTS'!$K$4:$K$842=" ")*('DETAIL 11-04 CTRTS'!$E$4:$E$842)) I need to
sum column E based upon a date condition in A and a blank in K. The above
returns 0.00.

When I use the above statement and change the K condition to 0 it returns
the sum of negative values meeting the date condition in A (the K value of
those records and only those records is some text). When the K condition is
<0 the statement returns 0.00. K is formatted as text But I need to also
test for a blank field.

Your help is appreciated as always

wal50

=SUMPRODUCT(--('DETAIL 11-04
CTRTS'!$A$4:$A$842=DATE(2004,11,2))*(ISBLANK('DETA IL 11-04
CTRTS'!$K$4:$K$842))*('DETAIL 11-04 CTRTS'!$E$4:$E$842))

perhaps

--
HTH

Bob Phillips

"wal50" wrote in message
...
=SUMPRODUCT(--('DETAIL 11-04 CTRTS'!$A$4:$A$842=DATE(2004,11,2))*('DETAIL
11-04 CTRTS'!$K$4:$K$842=" ")*('DETAIL 11-04 CTRTS'!$E$4:$E$842)) I need
to
sum column E based upon a date condition in A and a blank in K. The above
returns 0.00.

When I use the above statement and change the K condition to 0 it returns
the sum of negative values meeting the date condition in A (the K value of
those records and only those records is some text). When the K condition
is
<0 the statement returns 0.00. K is formatted as text But I need to also
test for a blank field.

Your help is appreciated as always

wal50

If K-range must be of zero-length...

=SUMPRODUCT(--('DETAIL 11-04
CTRTS'!$A$4:$A$842=DATE(2004,11,2)),--('DETAIL 11-04
CTRTS'!$K$4:$K$842=""),'DETAIL 11-04 CTRTS'!$E$4:$E$842)

If K-range is irrelevant, you would better switch to a formula with SumIf...

=SUMIF('DETAIL 11-04 CTRTS'!$A$4:$A$842,DATE(2004,11,2),'DETAIL 11-04
CTRTS'!$E$4:$E$842)

wal50 wrote:
=SUMPRODUCT(--('DETAIL 11-04 CTRTS'!$A$4:$A$842=DATE(2004,11,2))*('DETAIL
11-04 CTRTS'!$K$4:$K$842=" ")*('DETAIL 11-04 CTRTS'!$E$4:$E$842)) I need to
sum column E based upon a date condition in A and a blank in K. The above
returns 0.00.

When I use the above statement and change the K condition to 0 it returns
the sum of negative values meeting the date condition in A (the K value of
those records and only those records is some text). When the K condition is
<0 the statement returns 0.00. K is formatted as text But I need to also
test for a blank field.

Your help is appreciated as always

wal50

Both do the job. Thanks
wal50

"Bob Phillips" wrote:

=SUMPRODUCT(--('DETAIL 11-04
CTRTS'!$A$4:$A$842=DATE(2004,11,2))*(ISBLANK('DETA IL 11-04
CTRTS'!$K$4:$K$842))*('DETAIL 11-04 CTRTS'!$E$4:$E$842))

perhaps

--
HTH

Bob Phillips

"wal50" wrote in message
...
=SUMPRODUCT(--('DETAIL 11-04 CTRTS'!$A$4:$A$842=DATE(2004,11,2))*('DETAIL
11-04 CTRTS'!$K$4:$K$842=" ")*('DETAIL 11-04 CTRTS'!$E$4:$E$842)) I need
to
sum column E based upon a date condition in A and a blank in K. The above
returns 0.00.

When I use the above statement and change the K condition to 0 it returns
the sum of negative values meeting the date condition in A (the K value of
those records and only those records is some text). When the K condition
is
<0 the statement returns 0.00. K is formatted as text But I need to also
test for a blank field.

Your help is appreciated as always

wal50

Multiple criteria lookup
 

Sheet 1 has sales guys' names, Sheet 2 has sales results by month (Salesman A
has 3 sales $19 in Jan, 4 sales $20 in Feb, etc.)
I want to look up create a report showing the totals units and $ per month
for each guy. So, I want to match the guy's name in sheet 1 to his sales
records in sheet 2 within date ranges, then move the units and $ fields from
sheet 2 to sheet 1.
I did something similar before with sumproduct a while back but that was a
count. here I want to move fields based on a match.
Thanks for your help.
WAL

Multiple criteria lookup
 

Take a look at pivot tables


See http://www.contextures.com/xlPivot01.html
and http://www.peltierteh.com/Excel/Pivots/pivottables.htm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"wal50" wrote in message
...
Sheet 1 has sales guys' names, Sheet 2 has sales results by month
(Salesman A
has 3 sales $19 in Jan, 4 sales $20 in Feb, etc.)
I want to look up create a report showing the totals units and $ per month
for each guy. So, I want to match the guy's name in sheet 1 to his sales
records in sheet 2 within date ranges, then move the units and $ fields
from
sheet 2 to sheet 1.
I did something similar before with sumproduct a while back but that was a
count. here I want to move fields based on a match.
Thanks for your help.
WAL

Multiple criteria lookup
 

I had been doing a pivot table to get the info.
But they changed the requirement to do a cumulative report showing the last
X months.
My plan is to get the data for the each month into its own sheet, then do a
lookup/sumproduct/something that moves the info for each sales guy into the
his record on the cumulative master.
Using a pivot table to do this doesn't seem provide the same flexibility in
the final report - or do I need to know more about the pivot table
functionality? Plus , I thought if I did it through an equation(s), I could
just change the date range every month once I had them copied to the new
month's column and hide the old month columns.
Better ideas are always welcome.
WAL

"Bob Phillips" wrote:

Take a look at pivot tables


See http://www.contextures.com/xlPivot01.html
and http://www.peltierteh.com/Excel/Pivots/pivottables.htm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"wal50" wrote in message
...
Sheet 1 has sales guys' names, Sheet 2 has sales results by month
(Salesman A
has 3 sales $19 in Jan, 4 sales $20 in Feb, etc.)
I want to look up create a report showing the totals units and $ per month
for each guy. So, I want to match the guy's name in sheet 1 to his sales
records in sheet 2 within date ranges, then move the units and $ fields
from
sheet 2 to sheet 1.
I did something similar before with sumproduct a while back but that was a
count. here I want to move fields based on a match.
Thanks for your help.
WAL

Multiple criteria lookup
 

Okay.

Assumptions about Sheet 2

A2:A25 - salesman's name
B2:B25 - date of sales
C2:C25 - amount of sales

Then, on Sheet1

A1: Year for report
B1: = DATE($A$1,COLUMN(A1),1), copy across to M, and format as required
(such as mmm)
Salesman's names in A2 down
B2:
=SUMPRODUCT(--(Sheet2!$A$2:$A$25=$A2),--(YEAR(Sheet2!B2:B25)=YEAR(B$1)),--(M
ONTH(Sheet2!$B$2:$B$25)=MONTH(B$1)),Sheet2!$C$2:$C $25)

copy down and across

Just change the ranges to suit, or better use range names.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"wal50" wrote in message
...
I had been doing a pivot table to get the info.
But they changed the requirement to do a cumulative report showing the
last
X months.
My plan is to get the data for the each month into its own sheet, then do
a
lookup/sumproduct/something that moves the info for each sales guy into
the
his record on the cumulative master.
Using a pivot table to do this doesn't seem provide the same flexibility
in
the final report - or do I need to know more about the pivot table
functionality? Plus , I thought if I did it through an equation(s), I
could
just change the date range every month once I had them copied to the new
month's column and hide the old month columns.
Better ideas are always welcome.
WAL

"Bob Phillips" wrote:

Take a look at pivot tables


See http://www.contextures.com/xlPivot01.html
and http://www.peltierteh.com/Excel/Pivots/pivottables.htm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"wal50" wrote in message
...
Sheet 1 has sales guys' names, Sheet 2 has sales results by month
(Salesman A
has 3 sales $19 in Jan, 4 sales $20 in Feb, etc.)
I want to look up create a report showing the totals units and $ per
month
for each guy. So, I want to match the guy's name in sheet 1 to his
sales
records in sheet 2 within date ranges, then move the units and $
fields
from
sheet 2 to sheet 1.
I did something similar before with sumproduct a while back but that
was a
count. here I want to move fields based on a match.
Thanks for your help.
WAL