Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & multiple criteria - one answer
I am trying to use sumproduct to scan a worksheet for two criiteria that
match and supply one value that matches the data type row (i.e. Total) under the appropriate date column (i.e. 12/04/07). I am using the following statement " =SUMPRODUCT(('Daily Entry Logs'!C1:D1='2007 Daily Sheet'!C4)*('Daily Entry Logs'!B2:B48='2007 Daily Sheet'!B9)*('Daily Entry Logs'!C17:W48)) Source Sheet Daily Entry Logs Cell A Cell B Cell C Cell D Row 1 12/03/07 12/04/07 Row 2 FLAG POSTINGS Total 1 2 Row 3 Misallocations 2 3 Row 4 Other Work Stack 3 4 Target Sheet 2007 Daily Sheet Cell A Cell B Cell C Row 4 DATE December 4, 2007 Row 8 FLAG POSTINGS (FP'S, AF'S) Row 9 Total #N/A (should be 2) Row 10 Misallocations #N/A (should be 3) Row 11 Other Work Stack #N/A (should be 4) AS you can see I get the #N/A error, I have data in all of the source cells the minimum being zero (0). It seems that if I reference the date cells as a range ift doesn't work and if I reference the source data cells it also doesn't work. Canyone help out? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & multiple criteria - one answer
I haven't really examined your scenario, *BUT* ... what I see ... right off
the bat, is that the range sizes in your formula are *not* equal! B2:B48 versus C17:W48 This can cause a #N/A error. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tony" wrote in message ... I am trying to use sumproduct to scan a worksheet for two criiteria that match and supply one value that matches the data type row (i.e. Total) under the appropriate date column (i.e. 12/04/07). I am using the following statement " =SUMPRODUCT(('Daily Entry Logs'!C1:D1='2007 Daily Sheet'!C4)*('Daily Entry Logs'!B2:B48='2007 Daily Sheet'!B9)*('Daily Entry Logs'!C17:W48)) Source Sheet Daily Entry Logs Cell A Cell B Cell C Cell D Row 1 12/03/07 12/04/07 Row 2 FLAG POSTINGS Total 1 2 Row 3 Misallocations 2 3 Row 4 Other Work Stack 3 4 Target Sheet 2007 Daily Sheet Cell A Cell B Cell C Row 4 DATE December 4, 2007 Row 8 FLAG POSTINGS (FP'S, AF'S) Row 9 Total #N/A (should be 2) Row 10 Misallocations #N/A (should be 3) Row 11 Other Work Stack #N/A (should be 4) AS you can see I get the #N/A error, I have data in all of the source cells the minimum being zero (0). It seems that if I reference the date cells as a range ift doesn't work and if I reference the source data cells it also doesn't work. Canyone help out? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct & multiple criteria - one answer
Looks like you'd be better off using INDEX/MATCH, i.e. something like
=INDEX('Daily Entry Logs'!C2:W48,MATCH('2007 Daily Sheet'!B9,'Daily Entry Logs'!B2:B48,0),MATCH('2007 Daily Sheet'!C4,'Daily Entry Logs'!C1:W1,0)) "Ragdyer" wrote: I haven't really examined your scenario, *BUT* ... what I see ... right off the bat, is that the range sizes in your formula are *not* equal! B2:B48 versus C17:W48 This can cause a #N/A error. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tony" wrote in message ... I am trying to use sumproduct to scan a worksheet for two criiteria that match and supply one value that matches the data type row (i.e. Total) under the appropriate date column (i.e. 12/04/07). I am using the following statement " =SUMPRODUCT(('Daily Entry Logs'!C1:D1='2007 Daily Sheet'!C4)*('Daily Entry Logs'!B2:B48='2007 Daily Sheet'!B9)*('Daily Entry Logs'!C17:W48)) Source Sheet Daily Entry Logs Cell A Cell B Cell C Cell D Row 1 12/03/07 12/04/07 Row 2 FLAG POSTINGS Total 1 2 Row 3 Misallocations 2 3 Row 4 Other Work Stack 3 4 Target Sheet 2007 Daily Sheet Cell A Cell B Cell C Row 4 DATE December 4, 2007 Row 8 FLAG POSTINGS (FP'S, AF'S) Row 9 Total #N/A (should be 2) Row 10 Misallocations #N/A (should be 3) Row 11 Other Work Stack #N/A (should be 4) AS you can see I get the #N/A error, I have data in all of the source cells the minimum being zero (0). It seems that if I reference the date cells as a range ift doesn't work and if I reference the source data cells it also doesn't work. Canyone help out? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct With Multiple criteria | Excel Worksheet Functions | |||
Lookup Multiple Criteria return One answer | Excel Worksheet Functions | |||
multiple criteria in if or sumproduct | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions | |||
sumproduct using multiple criteria | Excel Worksheet Functions |