sumproduct multiple selections

[anand]

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:HV$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

 

[T. Valko]

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:

<space>ward
txward<space>

Biff

 

[Teethless mama]

=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))

 

[driller]

maybe too long but just tr
=--(SUMPRODUCT((FO$2:FO$5709={25,26,43,44,45})*(HV$2:HV$5709=1))+SUMPRODUCT((((G$2:G$5709={"ward","txward"})*(HV$2:HV$5709=1)))))