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

1. ## 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. ## 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)

3. ## 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!

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

#### 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