Counting the # of values in a range ???


G

GoBucks

I'm looking for a formula that will Count the # of months of the duration of
a project (row). In this example, I want a formula to calculate the # of
months from beg to end months. This example below (Row 2) is assuming the
project started in JAN and ended in Jun. So the result should be 6 months. So
I want to count the months with zeroes in between the beg and end months. In
my spreadsheet, I have columns going out to Dec-08. So there are 0's in every
month after Jun in Row 2 in my spreadsheet. Is there COUNT or maybe a
SUMPRODUCT formula that will achieve this? Thanks!!


Row Col B Col C Col D Col E Col F Col G Col F
Col G
1 Jan-08 Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08 Proj
Month #
2 234 91 75 0 0 13
0 Formula? (=6)
3 0 4 0 59 0 0
0 Formula? (=3)
 
Ad

Advertisements

G

GoBucks

Cleaned up the example:

1 Jan Feb Mar Apr May Jun Jul Proj Mon #
2 234 91 75 0 0 13 0 Formula? (=6)
3 0 4 0 59 0 0 0 Formula? (=3)
 
T

T. Valko

Cleaned up the example

That's much better!

Ok, are the numbers in the range *always* positive numbers?

Are the dates in your headers true Excel dates?
 
G

GoBucks

Yes, the numbers will always be positve. And yes, the header dates are true
Excel numbers (i.e. Jan-08 = 1/1/08)

Thanks and look fed to your response!
 
T

T. Valko

Assume your date headers are in the range A1:L1 (Jan to Dec dates)

Data in row 2 on down.

Entered in M2 and copied down as needed:

=MONTH(LOOKUP(1E100,1/A2:L2,A$1:L$1))-MATCH(TRUE,INDEX(A2:L2>0,0),0)+1

If there are no numbers >0 then the formula returns #N/A
 
G

GoBucks

Thank you! That is awesome!! But I have one other ? What if my date headers
extend from Jan-08 to Feb-09? If I use the formula and adjust the range for
the 14 Mo. range, there are negative values returned for rows with data in
the Jan-09 or Feb-09 columns. Is there a way to adjust the formula to work
for more than 12 month range? Thanks!
 
Ad

Advertisements

T

T. Valko

Ok, let's try this array formula**.

Assumes there are no text entries in the range. This version does not depend
on the dates in the column headers.

=MAX((A2:Q2>0)*COLUMN(A2:Q2))-MIN(IF(A2:Q2>0,COLUMN(A2:Q2)))+(COUNTIF(A2:Q2,">0")>0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
G

GoBucks

That did it!! Brilliant. Much appreciated.

T. Valko said:
Ok, let's try this array formula**.

Assumes there are no text entries in the range. This version does not depend
on the dates in the column headers.

=MAX((A2:Q2>0)*COLUMN(A2:Q2))-MIN(IF(A2:Q2>0,COLUMN(A2:Q2)))+(COUNTIF(A2:Q2,">0")>0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top