Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct multiple selections
I'm trying to use the sumproduct function to look at multiple items in one
column and one column in another. The text form is =SUMPRODUCT((G$2:G$5709={"ward","txward"})*(HV$2:H V$5709=1)) The numeric version is: =SUMPRODUCT((FO$2:FO$5709={25,26,43,44,45})*(HV$2: HV$5709=1)) Neither returns the correct answer. In each case I'm trying to combine the first array as "or" i.e. if any of these these texts or, in 2nd case, numbers i first column and "1' in 2nd column, then return a "1" in the cell. Can anyone assist? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct multiple selections
There's nothing wrong with either formula so you must have "data issues".
Numbers formatted as TEXT? Leading and/or trailing spaces with the text entries: <spaceward txward<space Biff "anand" wrote in message ... I'm trying to use the sumproduct function to look at multiple items in one column and one column in another. The text form is =SUMPRODUCT((G$2:G$5709={"ward","txward"})*(HV$2:H V$5709=1)) The numeric version is: =SUMPRODUCT((FO$2:FO$5709={25,26,43,44,45})*(HV$2: HV$5709=1)) Neither returns the correct answer. In each case I'm trying to combine the first array as "or" i.e. if any of these these texts or, in 2nd case, numbers i first column and "1' in 2nd column, then return a "1" in the cell. Can anyone assist? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct multiple selections
=SUMPRODUCT(ISNUMBER(SEARCH("ward",G$2:G$5709)*(HV $2:HV$5709=1))
If your numbers format as text then use " " around the numbers =SUMPRODUCT((FO$2:FO$5709={"25","26","43","44","45 "})*(HV$2:HV$5709=1)) "anand" wrote: I'm trying to use the sumproduct function to look at multiple items in one column and one column in another. The text form is =SUMPRODUCT((G$2:G$5709={"ward","txward"})*(HV$2:H V$5709=1)) The numeric version is: =SUMPRODUCT((FO$2:FO$5709={25,26,43,44,45})*(HV$2: HV$5709=1)) Neither returns the correct answer. In each case I'm trying to combine the first array as "or" i.e. if any of these these texts or, in 2nd case, numbers i first column and "1' in 2nd column, then return a "1" in the cell. Can anyone assist? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct multiple selections
maybe too long but just try
=--(SUMPRODUCT((FO$2:FO$5709={25,26,43,44,45})*(HV$2: HV$5709=1))+SUMPRODUCT((((G$2:G$5709={"ward","txwa rd"})*(HV$2:HV$5709=1))))) -- ***** birds of the same feather flock together.. "anand" wrote: I'm trying to use the sumproduct function to look at multiple items in one column and one column in another. The text form is =SUMPRODUCT((G$2:G$5709={"ward","txward"})*(HV$2:H V$5709=1)) The numeric version is: =SUMPRODUCT((FO$2:FO$5709={25,26,43,44,45})*(HV$2: HV$5709=1)) Neither returns the correct answer. In each case I'm trying to combine the first array as "or" i.e. if any of these these texts or, in 2nd case, numbers i first column and "1' in 2nd column, then return a "1" in the cell. Can anyone assist? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF or SUMPRODUCT to total cells containing multiple texts | Excel Worksheet Functions | |||
copy and paste with multiple selections in Microsoft Excel | Excel Discussion (Misc queries) | |||
multiple selections from drop down list | Excel Worksheet Functions | |||
Multiple selections in a drop down menu | Excel Worksheet Functions | |||
Multiple SumProduct conditions | Excel Worksheet Functions |