=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