Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
Filling in blanks. | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |