Hi this is my formula, it works great for getting a number value, but in this instance x3:x30000 has words in (TEXT) and I want it to return the TEXT instead it returns #Value!=SUMPRODUCT(--(SiteCalls!$B$3:$B$30000=AnalysisData!$B3)*(SiteCalls!$G$3:$G$30000=AnalysisData!$G3)*(SiteCalls!$L$3:$L$30000=AnalysisData!$L3)*(SiteCalls!$X$3:$X$30000))
Can anyone help?
What do you mean by return the text? SUMPRODUCT by its ver
y nature, and name, sums the product of numbers.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I know sumproduct , sums,
I need somthing to check the columns for the various criterias, and return me the text value it finds. if that makes sense. All records are unique.
Then you don't want SUMPRODUCT
=INDEX(SiteCalls!$X$3:$X$30000,
MATCH(1,(SiteCalls!$B$3:$B$30000=AnalysisData!$B3)*(SiteCalls!$G$3:$G$30000 =AnalysisData!$G3)*(SiteCalls!$L$3:$L$30000=AnalysisData!$L3),0)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
thanks mate , worked good
Don't forget to mark your threads "solved"
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
