sumproduct query

**luffield** · 06-29-2009, 08:04 AM

Hi All,

I have a formula which I have copied from one of your good selves and tried to adapt it. The formula is below. I a am getting #NA as my answer.

=SUMPRODUCT(ISNUMBER(FIND({"h","H"},B11:G11))*{0.5,1})

I have a person name in Colomn A and then 1 week B-G on a row and want to count number of days holiday.

Thanks

Mark

**NBVC** · 06-29-2009, 08:10 AM

What exactly are you trying to do?

Is there only an "h" or "H" in B11:G11 or is that letter amongst others?

**JONvdHeyden** · 06-29-2009, 08:12 AM

Hi Mark

Do you have any #N/A errors in B11:B11? If so you need to handle them there.

BTW, your formula could also be:

=SUMPRODUCT(ISNUMBER(SEARCH("h",B11:G11))*{0.5,1})

SEARCH is not case sensitive...

**daddylonglegs** · 06-29-2009, 08:13 AM

The formula works for a column of data like A1:A10, for a row like B11:G11 you need to change the commas to semi-colons, i.e.

=SUMPRODUCT(ISNUMBER(FIND({"h";"H"},B11:G11))*{0.5;1})

edit: perhaps this formula would be better

=SUMPRODUCT(EXACT(B11:G11,{"h";"H"})*{0.5;1})

assumes that cells contain only the single letters