Adding text using sumproduct

[jamiesda]

Got sheet with 2 columns I need to add - suggested solution is to use sumproduct - which works. Only problem is that it counts only text as it appears in the calculation. I need to count the responses to a question and the location of the submitter - sumproduct works but will not pick up lines where the reponse doesn't exactly match. For example: need to count "yes" but some entries in sheet are "yes " (spaces) - using a wildcard i.e. "yes*" not working - any ideas?

[DonkeyOte]

You could either use SEARCH

=SUMPRODUCT(--(ISNUMBER(SEARCH("yes",A1:A100)),--(other test))

where A1:A100 = Answer range

or TRIM the answer range prior to evaluating

=SUMPRODUCT(--(TRIM(A1:A100)="yes"),--(other test))

[jamiesda]

Great the 1st one worked - much appreciated

One other quick Q - I need to replicate across multiple columns but when I do the calc uses subsequent columns - when pasted - how can I get round this?

thanks again...

[DonkeyOte]

Not sure I understand the question - are you perhaps talking of absolute/relative referencing ? http://www.ozgrid.com/Excel/free-tra...n-16-basic.htm