Counting the # of values in a range ???

  • Thread starter Thread starter GoBucks
  • Start date Start date
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)
 
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)
 
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?
 
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!
 
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
 
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!
 
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.
 
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.
 
Back
Top