3 Replies Latest reply on Jun 25, 2018 11:56 AM by Donald Olsen

# Conditional sum across a row of data (Emulate a hlookup in excel)

Hello all,

First time posting so please forgive me for any errors.

So what im trying to do is simulate a process I have in excel where I use "=IFERROR(HLOOKUP(\$B3,\$D\$2:\$W3,\$Y3,0),0)" in order to scan the row of data and only provide the sum of the first column that is not a zero, and no other afterward.

Essentially looking for the calculated column to represent the value of the first year only.

Sidebar I'd also like to add a filter to to use the data in the 2019 column if it is not 0 regardless of the value in 2018 example

Capture the values in the blue circles, and ignore the red because it is 2018

• ###### 1. Re: Conditional sum across a row of data (Emulate a hlookup in excel)

Hi Donald

You are pulling data from Oracle and have several sheets - otherwise I would recommend pivoting the data - it is a better solution but that may mess other things you are doing

see the attached

I wasn't able to tell if each group had a common min date

but for each group the min date is (if you can pivot the data this can all be done in a single formula

after you do this 4 times the min of the min is

min is a binary function so nesting was required to get the min of the 4

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Conditional sum across a row of data (Emulate a hlookup in excel)

Thank you for the quick response. I will have to try and manually recreate what you did at the moment I have Tableau 10.1  and will need to have my company approve the upgrade.

• ###### 3. Re: Conditional sum across a row of data (Emulate a hlookup in excel)

Hi Jim,

How can I modify to account for where I have a zero in one of the data points. As you have done it it works in all cases as long as none of the sum's are not zero?

It's almost there where as you suggest I can do a pivot on the result but with this slight issue.