SUMPRODUCT not returning TEXT value

C

CLR

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)*(All12!$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
 
P

Pecoflyer

CLR;332763 said:
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)*(All12!$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. :)
 
B

Bernie Deitrick

Chuck,

If only one pair matches

=INDEX(All12!$K:$K,SUMPRODUCT((All12!$A$12:$A$15000=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=DATA!$A$1)*(All12!$Q$12:$Q$15000=DATA!$K$2),ROW(K12:K15000))))

HTH,
Bernie
MS Excel MVP
 

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