First non-zero cell in a range of columnal cells

Posted on 2013-09-18
Medium Priority
Last Modified: 2013-09-19
Previously I had asked how to get the value from a cell which lines up with the first nonzero cell in a range. Things are a LITTLE more complicated now.

Referencing the picture. I need to find the value in column c for the first F/G/H row that has a non-zero value (in this case row 138).

The answer I received before was selection as the solution. And it worked for a while until the client did something new--she didn't put a value in F/G/ AND H.

So I have to account for rows which have 1, 2, or 3 values in a row.

I'm only interested in which is the first row encountered with a value in F/G OR H.

(Must be formula. Some clients will not allow macros to run on their machines).

Excel 2010.
Question by:author3000
LVL 25

Expert Comment

by:Ejgil Hedegaard
ID: 39504336
Column I1: =IF(SUM(F1:H1)>0,1,0) copy down.

Then =MATCH(1,I:I,0) will find the first 1, = row number.
LVL 50

Accepted Solution

barry houdini earned 1000 total points
ID: 39504346
Assuming you have data in rows 130 to 140 (adjust as required), you can use this "array formula"


confirmed with CTRL+SHIFT+ENTER

regards, barry
LVL 81

Assisted Solution

byundt earned 1000 total points
ID: 39504566
If the client is using Excel 2010 or later, you can take advantage of the AGGREGATE function:

The above formula does not need to be array-entered.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 39505760
Will the Aggregate function work on a 2010 Excel spreadsheet that is run on a computer running, oh, Windows XP, if the compatability packs have been installed?
LVL 81

Expert Comment

ID: 39505842
It doesn't matter if you have compatibility packs--you need to be using Excel 2010 or later for the AGGREGATE function to work.

Author Closing Comment

ID: 39505843
I have chosen these two answers because

(a) they require only one cell to have a formula.

This is important because when clients make requests for changes the fewer the number of cells there are to update the more efficient that process is. In addition, formulae need to be hidden and having formulae in column I would require I to be hidden. There is a VERY good chance it would get overlooked on an upgrade. It's a good solution but just just a good fit in this case.

(b) My clients variously use Excel 2003 and forward all the way to the latest. Having a solution that fits both pre-2010 and one that fits later helps us a lot. When we get into an older version spreadsheet the mindset shifts to just what works in the pre-2010 version. The same paradigm applies with the 2010 and 2010+ versions.

Thanks for the solutions. The spreadsheet development is moving forward again.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question