sumproduct multiple selections

A

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

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
 
T

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top