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)




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)


"GoBucks" wrote:

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

That's much better!

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

Are the dates in your headers true Excel dates?

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...

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)




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)


"GoBucks" wrote:

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)

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. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...

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)




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)


"GoBucks" wrote:

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)

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:L20,0),0)+1

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

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
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. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...

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)




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)


"GoBucks" wrote:

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)

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!

"T. Valko" wrote:

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:L20,0),0)+1

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

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
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. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...

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)




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)


"GoBucks" wrote:

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)

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:Q20)*COLUMN(A2:Q2))-MIN(IF(A2:Q20,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.

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
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!

"T. Valko" wrote:

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:L20,0),0)+1

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

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
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. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...

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)




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)


"GoBucks" wrote:

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)

That did it!! Brilliant. Much appreciated.

"T. Valko" wrote:

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:Q20)*COLUMN(A2:Q2))-MIN(IF(A2:Q20,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.

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
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!

"T. Valko" wrote:

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:L20,0),0)+1

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

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
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. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...

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)




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)


"GoBucks" wrote:

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)

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
That did it!! Brilliant. Much appreciated.

"T. Valko" wrote:

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:Q20)*COLUMN(A2:Q2))-MIN(IF(A2:Q20,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.

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
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!

"T. Valko" wrote:

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:L20,0),0)+1

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

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...
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. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"GoBucks" wrote in message
...

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)




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)


"GoBucks" wrote:

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)