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('DETAIL 11-04
CTRTS'!$K$4:$K$842))*('DETAIL 11-04 CTRTS'!$E$4:$E$842))

perhaps

--
HTH

Bob Phillips

"wal50" <wal50@discussions.microsoft.com> wrote in message
news:4081A275-05D8-4555-A6B8-99261C9C1394@microsoft.com...
> =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('DETAIL 11-04
> CTRTS'!$K$4:$K$842))*('DETAIL 11-04 CTRTS'!$E$4:$E$842))
>
> perhaps
>
> --
> HTH
>
> Bob Phillips
>
> "wal50" <wal50@discussions.microsoft.com> wrote in message
> news:4081A275-05D8-4555-A6B8-99261C9C1394@microsoft.com...
> > =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
>
>
>

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

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" <wal50@discussions.microsoft.com> wrote in message
news:C56E21EC-69B9-461F-B116-21075D8DA7EA@microsoft.com...
> 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
>

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" <wal50@discussions.microsoft.com> wrote in message
> news:C56E21EC-69B9-461F-B116-21075D8DA7EA@microsoft.com...
> > 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
> >
>
>
>

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" <wal50@discussions.microsoft.com> wrote in message
news:A2F65666-6D8E-43B9-A2BE-560803200351@microsoft.com...
> 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" <wal50@discussions.microsoft.com> wrote in message
> > news:C56E21EC-69B9-461F-B116-21075D8DA7EA@microsoft.com...
> > > 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
> > >
> >
> >
> >