Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT not returning TEXT value
Hi All....
All12! column K contains TEXT, like A+ This formula works fine if I use a different column than K which contains numbers but will not return the A+ value from column K......returns #VALUE! instead. =SUMPRODUCT((All12!$A$12:$A$15000=DATA!$A$1)*(All1 2!$Q$12:$Q$15000=DATA!$K$2)*(All12!$K$12:$K$15000) ) Is there a way to return TEXT values from column K if values in A and Q meet the criteria? TIA Vaya con Dios, Chuck, CABGx3 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT not returning TEXT value
CLR;332763 Wrote: Hi All.... All12! column K contains TEXT, like A+ This formula works fine if I use a different column than K which contains numbers but will not return the A+ value from column K......returns #VALUE! instead. =SUMPRODUCT((All12!$A$12:$A$15000=DATA!$A$1)*(All1 2!$Q$12:$Q$15000=DATA!$K$2)*(All12!$K$12:$K$15000) ) Is there a way to return TEXT values from column K if values in A and Q meet the criteria? TIA Vaya con Dios, Chuck, CABGx3 Hi, SUMPRODUCT will not return values, but sum or count. Maybe you need to add (All12!$K$12:$K$15000="A+") ? HOW TO GET FURTHER HELP WITH A WORKBOOK For further help with it why not join our forums (shown in the link below) it's completely free, if you do join you will have the opportunity to add attachmnets to your posts so you can add workbooks to better illustrate your problems and get help directly with them. Also if you do join please post in this thread (link found below) so that people who have been following or helping with this query can continue to do so. :) -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92992 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT not returning TEXT value
Chuck,
If only one pair matches =INDEX(All12!$K:$K,SUMPRODUCT((All12!$A$12:$A$1500 0=DATA!$A$1)*(All12!$Q$12:$Q$15000=DATA!$K$2)*ROW( K12:K15000)) Otherwise, you need to limit to one match - enter using Ctrl-Shift-Enter =INDEX(All12!$K:$K,MIN(IF((All12!$A$12:$A$15000=DA TA!$A$1)*(All12!$Q$12:$Q$15000=DATA!$K$2),ROW(K12: K15000)))) HTH, Bernie MS Excel MVP "CLR" wrote in message ... Hi All.... All12! column K contains TEXT, like A+ This formula works fine if I use a different column than K which contains numbers but will not return the A+ value from column K......returns #VALUE! instead. =SUMPRODUCT((All12!$A$12:$A$15000=DATA!$A$1)*(All1 2!$Q$12:$Q$15000=DATA!$K$2)*(All12!$K$12:$K$15000) ) Is there a way to return TEXT values from column K if values in A and Q meet the criteria? TIA Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct not returning data | Excel Worksheet Functions | |||
SUMPRODUCT returning no answer | Excel Worksheet Functions | |||
Help on sumproduct returning errors | Excel Worksheet Functions | |||
sumproduct returning #NA | Excel Discussion (Misc queries) | |||
Sumproduct returning #NUM! | Excel Worksheet Functions |