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

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

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

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