Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding a counting function within SumProduct
Experts: I can't figure this out... I need to add another array to this
formula. =SUMPRODUCT(--(A3:A236="John Gay"),--(W3:W236=--"2008-01-01"),--(W3:W236<=--"2008-12-31")) In array #1 I am searched "column A" for the name John Gay. In arrays #2 & #3 I am searching "column W" for entry's within a date range. My problem is this: I have another column (column Y) that has numerical entry's. This column represents the quantity of products that John Gay sold on any given date within the date range. Most of the entry's in "column Y" have a value larger than 1. How can I modify this formula to count the actual total of the numerical values in "column Y" after array #1, #2 & #3 have been recognized as true or false? I hope I have explained my issue clearly... Anyones help would be greatly appreciated. Thanks... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding a counting function within SumProduct
TRY THIS...
=SUMPRODUCT(--(A3:A236="John Gay"),--(W3:W236=--"2008/01/01"),-- (W3:W236<=--"2008-12-31"),--(X3:X236<"")) It assumes that the numerical value is in column . You can also change it to total the numerical values by changing the --(X3:X236<"") to x3:x236 On Jun 20, 8:13 am, PBB wrote: Experts: I can't figure this out... I need to add another array to this formula. =SUMPRODUCT(--(A3:A236="John Gay"),--(W3:W236=--"2008-01-01"),--(W3:W236<=--"2008-12-31")) In array #1 I am searched "column A" for the name John Gay. In arrays #2 & #3 I am searching "column W" for entry's within a date range. My problem is this: I have another column (column Y) that has numerical entry's. This column represents the quantity of products that John Gay sold on any given date within the date range. Most of the entry's in "column Y" have a value larger than 1. How can I modify this formula to count the actual total of the numerical values in "column Y" after array #1, #2 & #3 have been recognized as true or false? I hope I have explained my issue clearly... Anyones help would be greatly appreciated. Thanks... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding a counting function within SumProduct
Thanks TIM879 - This worked great !!
"Tim879" wrote: TRY THIS... =SUMPRODUCT(--(A3:A236="John Gay"),--(W3:W236=--"2008/01/01"),-- (W3:W236<=--"2008-12-31"),--(X3:X236<"")) It assumes that the numerical value is in column . You can also change it to total the numerical values by changing the --(X3:X236<"") to x3:x236 On Jun 20, 8:13 am, PBB wrote: Experts: I can't figure this out... I need to add another array to this formula. =SUMPRODUCT(--(A3:A236="John Gay"),--(W3:W236=--"2008-01-01"),--(W3:W236<=--"2008-12-31")) In array #1 I am searched "column A" for the name John Gay. In arrays #2 & #3 I am searching "column W" for entry's within a date range. My problem is this: I have another column (column Y) that has numerical entry's. This column represents the quantity of products that John Gay sold on any given date within the date range. Most of the entry's in "column Y" have a value larger than 1. How can I modify this formula to count the actual total of the numerical values in "column Y" after array #1, #2 & #3 have been recognized as true or false? I hope I have explained my issue clearly... Anyones help would be greatly appreciated. Thanks... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting non blanks with SUMPRODUCT? | Excel Discussion (Misc queries) | |||
counting function but not double counting duplicates | Excel Worksheet Functions | |||
counting and adding | Excel Discussion (Misc queries) | |||
counting and adding | Excel Discussion (Misc queries) | |||
do I need array formula or sumproduct for counting? | Excel Worksheet Functions |