I have a years worth of dates in Col B and need to do countif and countblank
in Col D but I need to seperate by quarters, I was thinking of doing it w/
an if statement but didn't work, I could sort by date and copy to individual
worksheets but I have 3 tables w/ 45K+ records each and need to count
multiple col.
I would prefer to do this a formula, If this can't be done I an do a short
vba script thanks Zb

=SUMPRODUCT(--($B$2:$B$2000<""),--(INT((MONTH($B$2:$B$2000)+2)/3)=1))

for quarter 1

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet wrote in message
...
I have a years worth of dates in Col B and need to do countif and
countblank
in Col D but I need to seperate by quarters, I was thinking of doing it
w/
an if statement but didn't work, I could sort by date and copy to
individual
worksheets but I have 3 tables w/ 45K+ records each and need to count
multiple col.
I would prefer to do this a formula, If this can't be done I an do a short
vba script thanks Zb

I'm sorry I was unclear when I worded the question the dates are in col b and
the values that are counted in col d

"Bob Phillips" wrote:

=SUMPRODUCT(--($B$2:$B$2000<""),--(INT((MONTH($B$2:$B$2000)+2)/3)=1))

for quarter 1

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet wrote in message
...
I have a years worth of dates in Col B and need to do countif and
countblank
in Col D but I need to seperate by quarters, I was thinking of doing it
w/
an if statement but didn't work, I could sort by date and copy to
individual
worksheets but I have 3 tables w/ 45K+ records each and need to count
multiple col.
I would prefer to do this a formula, If this can't be done I an do a short
vba script thanks Zb

add the Ds

=SUMPRODUCT(--($B$2:$B$2000<""),--(INT((MONTH($B$2:$B$2000)+2)/3)=1),$D$2:$D$2000)--HTHBob(there's no email, no snail mail, but somewhere should be gmail in my addy)"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet wrote in ... I'm sorry I was unclear when I worded the question the dates are in col band the values that are counted in col d "Bob Phillips" wrote: =SUMPRODUCT(--($B$2:$B$2000<""),--(INT((MONTH($B$2:$B$2000)+2)/3)=1)) for quarter 1 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in myaddy) "Zb Kornecki" <zDOTkorneckiATcomcastDOTnet wrote in message ... I have a years worth of dates in Col B and need to do countif and countblank in Col D but I need to seperate by quarters, I was thinking of doingit w/ an if statement but didn't work, I could sort by date and copy to individual worksheets but I have 3 tables w/ 45K+ records each and need to count multiple col. I would prefer to do this a formula, If this can't be done I an do ashort vba script thanks Zb

This formula counts how many of the dates are in first quarter:
=SUMPRODUCT(--((INT((MONTH(B2:B103)-1)/3)+1)=1))
This counts how many are in Q1 of year 2007
=SUMPRODUCT(--((INT((MONTH(B2:B103)-1)/3)+1)=1),--(YEAR(B2:B103)=2007))
Or course, the =1 and =2007 parts of the formula can be replaces by =D1, =E2
for example
How does SUMPRODUCT work? See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet wrote in message
...
I have a years worth of dates in Col B and need to do countif and
countblank
in Col D but I need to seperate by quarters, I was thinking of doing it
w/
an if statement but didn't work, I could sort by date and copy to
individual
worksheets but I have 3 tables w/ 45K+ records each and need to count
multiple col.
I would prefer to do this a formula, If this can't be done I an do a short
vba script thanks Zb

Thank's for the help, The info links were excellent, I'd rather learn how to
do then have to keep asking. thanks again Zb

"Bernard Liengme" wrote:

This formula counts how many of the dates are in first quarter:
=SUMPRODUCT(--((INT((MONTH(B2:B103)-1)/3)+1)=1))
This counts how many are in Q1 of year 2007
=SUMPRODUCT(--((INT((MONTH(B2:B103)-1)/3)+1)=1),--(YEAR(B2:B103)=2007))
Or course, the =1 and =2007 parts of the formula can be replaces by =D1, =E2
for example
How does SUMPRODUCT work? See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Zb Kornecki" <zDOTkorneckiATcomcastDOTnet wrote in message
...
I have a years worth of dates in Col B and need to do countif and
countblank
in Col D but I need to seperate by quarters, I was thinking of doing it
w/
an if statement but didn't work, I could sort by date and copy to
individual
worksheets but I have 3 tables w/ 45K+ records each and need to count
multiple col.
I would prefer to do this a formula, If this can't be done I an do a short
vba script thanks Zb