+ Reply to Thread
Results 1 to 3 of 3

Looking up values based on a field of text/ number, multiple columns

  1. #1
    Registered User
    Join Date
    09-25-2014
    Location
    Oslo, Norway
    MS-Off Ver
    Excel Mac 2011
    Posts
    11

    Question Looking up values based on a field of text/ number, multiple columns

    Hi

    I will try to make this easy, and I am sure that the solution of this problem is simple, but I am just not sure.

    I need to calculate total value based on "x", the name and "totalt". Meaning, I need to make an formula that works, for example how much Teo has billed in value. The example with Teo would include the numbers: 11550+10500+2100+1485 (I have a lot of rows beneath in my excel sheet).

    The question: How do I write a formula for Teo, Erik, Sven etc to calculate those numbers based on the "x" I put in?

    Thanks for replies on this matter!

    Sincerely,

    Christian

    Skjermbilde 2014-09-25 kl. 15.07.49.png

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking up values based on a field of text/ number, multiple columns

    Maybe this...

    Data Range
    A
    B
    C
    D
    1
    Name1
    Name2
    Name3
    2
    31
    3
    55
    X
    X
    4
    79
    5
    9
    6
    44
    X
    X
    X
    7
    65
    X
    8
    52
    X
    9
    19
    X
    10
    15
    X
    11
    Totals
    118
    96
    179


    This formula entered in B11 and copied across:

    =SUMIF(B2:B10,"x",$A2:$A10)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Looking up values based on a field of text/ number, multiple columns

    Use Control + Shift + Enter to enter this Array Formula

    Where B2:E2 is the row with the x's on it, and B10:E10 is the row with the values to be added.
    =SUM((B2:E2="x")*$B$10:$E$10)


    Edit: How exactly did I forget the SUMIF function exists. Ugh. Use Tony's one, its easier

    Also I did mine sideways, haha. Too early for this!
    Last edited by Speshul; 09-25-2014 at 09:29 AM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Can't append leading zero to text field populated with number values
    By khughes46 in forum Excel General
    Replies: 4
    Last Post: 07-10-2014, 06:30 PM
  2. Replies: 7
    Last Post: 03-24-2014, 09:12 AM
  3. [SOLVED] Requesting help returning text values from criteria based on multiple columns
    By dzarrabi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-15-2013, 08:16 AM
  4. Calculate field based on multiple columns and rows
    By Fade-e in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-30-2013, 02:27 AM
  5. Replies: 3
    Last Post: 05-03-2010, 11:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1