Share via

Sumproduct to Return Text Value

Anonymous
2010-07-11T07:27:19+00:00

Managers of a Company are given Groups A to E and two managers from each group can sign/approve a budget as below

col O    col P   col Q

A           B           Ulimited

A           C            10,000,000

A            D             5,000,000

A             E            1,000,000

B             C            5,000,000

B              D           1,000,000

B              E               500,000

C              D              500,000

C               E               250,000

D               E                100,000

in Cell B5 and Cell C5 user enters

Group Names  (i.e  A, or B or C or D or E)and D5 calculates the Limit this pair can sign

In D5 i have follow Formula 

=SUMPRODUCT((O1:O10=B5)*(P1:P10=C5),(Q1:Q10))

its works fine  but when the user enters

"A" in B5 and "A" in C5 it returns zero.

I want sumproduct to return the Word "unlimited"

how can i do that

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Micky Avidan *** מיקי אבידן 16,371 Reputation points MVP Volunteer Moderator
2010-07-11T07:52:59+00:00

Why SUMPRODUCT !?

Try: {=INDEX(Q1:Q10,MATCH(B5&C5,O1:O10&P1:P10))}Pls note ! This is an Array Formula. You should NOT type the curly braces. In order to confirm such a formula, you will use the three key combination - while holding, down, CTRL+SHIFT press ENTER instead of just pressing ENTER. You will be able to identify an Array Formula, in the Formula Bar, if it is confined in a pair of curly braces.

If my reply resolved your question/problem - please check it as 'Answered'.

If it was only for some help, please click the 'Vote as Helpful' button. Thanks.

Micky, Microsoft® Excel MVP [2009-2010] - ISRAEL

Was this answer helpful?

0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-07-11T09:27:39+00:00

    Michael,

    I'd be careful with that formula, there are instances in which it will return the wrong value. With this data set in O1:Q10

    A B unlimited
    A C 1001
    A D 1002
    A E 1003
    B C 1004
    B D 1005
    B E 1006
    C D 1007

    | C | E | 1008 | | D | E | 1009 |

    With either B5 & C5 blank it will return 1009

    a typo of (say) A & R in B5:C5 returms 1003.

    I only did limited testing but the reaults become very unpredictable on un-sorted data.

    I think a more reliable solution is this array

    =INDEX(Q1:Q10,MATCH(1,(O1:O10=B5)*(P1:P10=C5),0))

    If this post answers your question, please mark it as the Answer.

    Mike H

    Was this answer helpful?

    0 comments
  2. Micky Avidan *** מיקי אבידן 16,371 Reputation points MVP Volunteer Moderator
    2010-07-11T08:46:04+00:00

    I'm glad I could help.

    As far as I recall, SUMPRODUCT intends to return numeric values, - not 'Strings'.

    If my reply resolved your question/problem - please check it as 'Answered'.

    If it was only for some help, please click the 'Vote as Helpful' button. Thanks.

    Micky, Microsoft® Excel MVP [2009-2010] - ISRAEL

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-07-11T08:02:02+00:00

    Thanks Michael. Works perfect.

    Wonder why it was not working with sumproduct. .when all other combinations give correct answers except this one...

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-07-11T07:29:04+00:00

    Sorry A Typo

    its works fine but when the user enters

    "A" in B5 and "B" in C5 it returns zero.

    I want sumproduct to return the Word "unlimited"

    Was this answer helpful?

    0 comments