I have a spreadsheet that calculates various percentages and what not based
on tips for days, weeks, and months. I need a formula that can take data from
a colum containg dates in the d-mmm format, and count how many days I have
worked in that month. I list each day seperatly, so I was thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot. Please Help...

Ok, I messed with it some more, and have it half way working...

I made the formula:

=COUNTIF(B2:B299, ""&I21) where I21 is the cell storing the month name, the
date I worked being in the range B2:B299. Now, the problem is, even if I go
past the month of June for example, it still counts it because I don't know
how to stop it.

Say July is stored in cell I22, I need something like =COUNTIF(B2:B299,
""&I21&<I22) but that will of course not work.

"GoodTrouble" wrote:

I have a spreadsheet that calculates various percentages and what not based
on tips for days, weeks, and months. I need a formula that can take data from
a colum containg dates in the d-mmm format, and count how many days I have
worked in that month. I list each day seperatly, so I was thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot. Please Help...

This counts the number of dates in A1:A20 that are month 7 (July)

=SUMPRODUCT(--(MONTH(A1:A20)=7))

HTH

"GoodTrouble" wrote:

I have a spreadsheet that calculates various percentages and what not based
on tips for days, weeks, and months. I need a formula that can take data from
a colum containg dates in the d-mmm format, and count how many days I have
worked in that month. I list each day seperatly, so I was thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot. Please Help...

it worked for me =COUNTIF(B2:B299, I21).What you are counting tho has to look
like whatever is in I21 so if you are counting dates say for june and you
have june dates in your column,a date of 1/6/2006 formated say mmm so it
shows up as June in I21,that wont count 2/6/2006,or 4/6/2006,it will only
count 1/6/2006
paul

remove nospam for email addy!



"GoodTrouble" wrote:

Ok, I messed with it some more, and have it half way working...

I made the formula:

=COUNTIF(B2:B299, ""&I21) where I21 is the cell storing the month name, the
date I worked being in the range B2:B299. Now, the problem is, even if I go
past the month of June for example, it still counts it because I don't know
how to stop it.

Say July is stored in cell I22, I need something like =COUNTIF(B2:B299,
""&I21&<I22) but that will of course not work.

"GoodTrouble" wrote:

I have a spreadsheet that calculates various percentages and what not based
on tips for days, weeks, and months. I need a formula that can take data from
a colum containg dates in the d-mmm format, and count how many days I have
worked in that month. I list each day seperatly, so I was thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot. Please Help...

Thanks for that! Finally solved many days of worthless work.

And now I have a harder one...

Is there a formula that I could use that would gather the column and cell
numbers for cells containing the months....that didn't make sense,

For instance,

B2:B299 is where I enter the date of which I work. There is no way to tell
how many days I will work in a month. The date being in the d-mmm format
stores part of the month, which I know Excel stores as a number anyway...so
how can I make a formula that would count how many days I worked in that
month...and then somehow take data that is in another coresponding column and
devide???

Like say column D held a different number, the devisor...so it would be like
B22/D22, only I need the totals, like How many days I worked, devided by the
total of all the numbers in column D

Make any sense??

"Toppers" wrote:

This counts the number of dates in A1:A20 that are month 7 (July)

=SUMPRODUCT(--(MONTH(A1:A20)=7))

HTH

"GoodTrouble" wrote:

I have a spreadsheet that calculates various percentages and what not based
on tips for days, weeks, and months. I need a formula that can take data from
a colum containg dates in the d-mmm format, and count how many days I have
worked in that month. I list each day seperatly, so I was thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot. Please Help...

Wouldn't just be the formula I have given divide by your cell?

=Sumproduct(month(b2:b299)=6)/Divisor

The SUMPRODUCT gives count of days worked in June ... but I don't fully
understand the divisor. You give an example of B22/D22 but B22 is a date(?)
so you cannot divide it.

Perhaps an example of the data would help me!




"GoodTrouble" wrote:

Thanks for that! Finally solved many days of worthless work.

And now I have a harder one...

Is there a formula that I could use that would gather the column and cell
numbers for cells containing the months....that didn't make sense,

For instance,

B2:B299 is where I enter the date of which I work. There is no way to tell
how many days I will work in a month. The date being in the d-mmm format
stores part of the month, which I know Excel stores as a number anyway...so
how can I make a formula that would count how many days I worked in that
month...and then somehow take data that is in another coresponding column and
devide???

Like say column D held a different number, the devisor...so it would be like
B22/D22, only I need the totals, like How many days I worked, devided by the
total of all the numbers in column D

Make any sense??

"Toppers" wrote:

This counts the number of dates in A1:A20 that are month 7 (July)

=SUMPRODUCT(--(MONTH(A1:A20)=7))

HTH

"GoodTrouble" wrote:

I have a spreadsheet that calculates various percentages and what not based
on tips for days, weeks, and months. I need a formula that can take data from
a colum containg dates in the d-mmm format, and count how many days I have
worked in that month. I list each day seperatly, so I was thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot. Please Help...

Don't forget the coercer

=SUMPRODUCT(--(MONTH(B2:B29)=6))/Divisor

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Toppers" wrote in message
...
Wouldn't just be the formula I have given divide by your cell?

=Sumproduct(month(b2:b299)=6)/Divisor

The SUMPRODUCT gives count of days worked in June ... but I don't fully
understand the divisor. You give an example of B22/D22 but B22 is a
date(?)
so you cannot divide it.

Perhaps an example of the data would help me!




"GoodTrouble" wrote:

Thanks for that! Finally solved many days of worthless work.

And now I have a harder one...

Is there a formula that I could use that would gather the column and
cell
numbers for cells containing the months....that didn't make sense,

For instance,

B2:B299 is where I enter the date of which I work. There is no way to
tell
how many days I will work in a month. The date being in the d-mmm format
stores part of the month, which I know Excel stores as a number
anyway...so
how can I make a formula that would count how many days I worked in that
month...and then somehow take data that is in another coresponding
column and
devide???

Like say column D held a different number, the devisor...so it would be
like
B22/D22, only I need the totals, like How many days I worked, devided by
the
total of all the numbers in column D

Make any sense??

"Toppers" wrote:

This counts the number of dates in A1:A20 that are month 7 (July)

=SUMPRODUCT(--(MONTH(A1:A20)=7))

HTH

"GoodTrouble" wrote:

I have a spreadsheet that calculates various percentages and what
not based
on tips for days, weeks, and months. I need a formula that can take
data from
a colum containg dates in the d-mmm format, and count how many days
I have
worked in that month. I list each day seperatly, so I was thinking
that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot.
Please Help...

Hi

I wonder whether what the OP is looking for is

=SUMPRODUCT(--(MONTH(B2:B299)=6),D2:D229)/SUMPRODUCT(--(MONTH(A1:A20)=6))
This would give the average value per day for days worked in June.

--
Regards

Roger Govier


"Toppers" wrote in message
...
Wouldn't just be the formula I have given divide by your cell?

=Sumproduct(month(b2:b299)=6)/Divisor

The SUMPRODUCT gives count of days worked in June ... but I don't
fully
understand the divisor. You give an example of B22/D22 but B22 is a
date(?)
so you cannot divide it.

Perhaps an example of the data would help me!




"GoodTrouble" wrote:

Thanks for that! Finally solved many days of worthless work.

And now I have a harder one...

Is there a formula that I could use that would gather the column and
cell
numbers for cells containing the months....that didn't make sense,

For instance,

B2:B299 is where I enter the date of which I work. There is no way to
tell
how many days I will work in a month. The date being in the d-mmm
format
stores part of the month, which I know Excel stores as a number
anyway...so
how can I make a formula that would count how many days I worked in
that
month...and then somehow take data that is in another coresponding
column and
devide???

Like say column D held a different number, the devisor...so it would
be like
B22/D22, only I need the totals, like How many days I worked, devided
by the
total of all the numbers in column D

Make any sense??

"Toppers" wrote:

This counts the number of dates in A1:A20 that are month 7 (July)

=SUMPRODUCT(--(MONTH(A1:A20)=7))

HTH

"GoodTrouble" wrote:

I have a spreadsheet that calculates various percentages and what
not based
on tips for days, weeks, and months. I need a formula that can
take data from
a colum containg dates in the d-mmm format, and count how many
days I have
worked in that month. I list each day seperatly, so I was
thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot.
Please Help...

YES! This worked perfectly, and I was able to modify that formula to take
care of 4 other things I needed.

Much Thanks To Everyone!

"Roger Govier" wrote:

Hi

I wonder whether what the OP is looking for is

=SUMPRODUCT(--(MONTH(B2:B299)=6),D2:D229)/SUMPRODUCT(--(MONTH(A1:A20)=6))
This would give the average value per day for days worked in June.

--
Regards

Roger Govier


"Toppers" wrote in message
...
Wouldn't just be the formula I have given divide by your cell?

=Sumproduct(month(b2:b299)=6)/Divisor

The SUMPRODUCT gives count of days worked in June ... but I don't
fully
understand the divisor. You give an example of B22/D22 but B22 is a
date(?)
so you cannot divide it.

Perhaps an example of the data would help me!




"GoodTrouble" wrote:

Thanks for that! Finally solved many days of worthless work.

And now I have a harder one...

Is there a formula that I could use that would gather the column and
cell
numbers for cells containing the months....that didn't make sense,

For instance,

B2:B299 is where I enter the date of which I work. There is no way to
tell
how many days I will work in a month. The date being in the d-mmm
format
stores part of the month, which I know Excel stores as a number
anyway...so
how can I make a formula that would count how many days I worked in
that
month...and then somehow take data that is in another coresponding
column and
devide???

Like say column D held a different number, the devisor...so it would
be like
B22/D22, only I need the totals, like How many days I worked, devided
by the
total of all the numbers in column D

Make any sense??

"Toppers" wrote:

This counts the number of dates in A1:A20 that are month 7 (July)

=SUMPRODUCT(--(MONTH(A1:A20)=7))

HTH

"GoodTrouble" wrote:

I have a spreadsheet that calculates various percentages and what
not based
on tips for days, weeks, and months. I need a formula that can
take data from
a colum containg dates in the d-mmm format, and count how many
days I have
worked in that month. I list each day seperatly, so I was
thinking that some
variation of COUNTIF might work.

I have tried to get it to work many different ways, and cannot.
Please Help...