Hello! I'm writing a couple of formulas to track employee statistics. I
have my data set up as follows: Col.A is date, Col.D is "yes" or "no". These
are the only two columns with critical data. My summary/calculation area is
to the right of the populated columns. Col.F has a list of months (Nov-06,
Dec-06, so on...these are entered as 11-1-06, 12-1-06....but formatted to
display as Nov-06...). Col.G has a formula to count entries per month (or
leave blank if no entries):

=IF((COUNTIF($A$4:$A$200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7))0,(COUNTIF($A$4:$A$ 200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7)),"")

This works OK. Col.H will be "total correct" per month, using the yes or no
in Col.D to get a total for that month which are correct (a "yes" entry in
col.D). I've been trying to combine COUNTIF and AND, but no luck. Any
ideas? THANKS!!

I just responded to your post in the Access newsgroup. Use SUMPRODUCT.
--
Brevity is the soul of wit.


"ClintG" wrote:

Hello! I'm writing a couple of formulas to track employee statistics. I
have my data set up as follows: Col.A is date, Col.D is "yes" or "no". These
are the only two columns with critical data. My summary/calculation area is
to the right of the populated columns. Col.F has a list of months (Nov-06,
Dec-06, so on...these are entered as 11-1-06, 12-1-06....but formatted to
display as Nov-06...). Col.G has a formula to count entries per month (or
leave blank if no entries):

=IF((COUNTIF($A$4:$A$200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7))0,(COUNTIF($A$4:$A$ 200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7)),"")

This works OK. Col.H will be "total correct" per month, using the yes or no
in Col.D to get a total for that month which are correct (a "yes" entry in
col.D). I've been trying to combine COUNTIF and AND, but no luck. Any
ideas? THANKS!!

Hi Clint

You can make use of Sumproduct (this way you don't need 2 month values
either, just the one will do):

=SUMPRODUCT(--(TEXT($A$4:$A$200,"mmmyyyy")=TEXT($F6,"mmmyyyy")),--($D$4:$D$200="Yes"))

the above will count the number of Yes's in column D for the given
month as shown in F6. The "Yes" can obviously be a cell reference
instead holding the value "Yes" (or "no" as required).

Hope this helps!

Richard


ClintG wrote:

Hello! I'm writing a couple of formulas to track employee statistics. I
have my data set up as follows: Col.A is date, Col.D is "yes" or "no". These
are the only two columns with critical data. My summary/calculation area is
to the right of the populated columns. Col.F has a list of months (Nov-06,
Dec-06, so on...these are entered as 11-1-06, 12-1-06....but formatted to
display as Nov-06...). Col.G has a formula to count entries per month (or
leave blank if no entries):

=IF((COUNTIF($A$4:$A$200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7))0,(COUNTIF($A$4:$A$ 200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7)),"")

This works OK. Col.H will be "total correct" per month, using the yes or no
in Col.D to get a total for that month which are correct (a "yes" entry in
col.D). I've been trying to combine COUNTIF and AND, but no luck. Any
ideas? THANKS!!

Thanks Dave! I appreciate your help.

"Dave F" wrote:

I just responded to your post in the Access newsgroup. Use SUMPRODUCT.
--
Brevity is the soul of wit.


"ClintG" wrote:

Hello! I'm writing a couple of formulas to track employee statistics. I
have my data set up as follows: Col.A is date, Col.D is "yes" or "no". These
are the only two columns with critical data. My summary/calculation area is
to the right of the populated columns. Col.F has a list of months (Nov-06,
Dec-06, so on...these are entered as 11-1-06, 12-1-06....but formatted to
display as Nov-06...). Col.G has a formula to count entries per month (or
leave blank if no entries):

=IF((COUNTIF($A$4:$A$200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7))0,(COUNTIF($A$4:$A$ 200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7)),"")

This works OK. Col.H will be "total correct" per month, using the yes or no
in Col.D to get a total for that month which are correct (a "yes" entry in
col.D). I've been trying to combine COUNTIF and AND, but no luck. Any
ideas? THANKS!!

Thanks Richard! Works like a charm!

"RichardSchollar" wrote:

Hi Clint

You can make use of Sumproduct (this way you don't need 2 month values
either, just the one will do):

=SUMPRODUCT(--(TEXT($A$4:$A$200,"mmmyyyy")=TEXT($F6,"mmmyyyy")),--($D$4:$D$200="Yes"))

the above will count the number of Yes's in column D for the given
month as shown in F6. The "Yes" can obviously be a cell reference
instead holding the value "Yes" (or "no" as required).

Hope this helps!

Richard


ClintG wrote:

Hello! I'm writing a couple of formulas to track employee statistics. I
have my data set up as follows: Col.A is date, Col.D is "yes" or "no". These
are the only two columns with critical data. My summary/calculation area is
to the right of the populated columns. Col.F has a list of months (Nov-06,
Dec-06, so on...these are entered as 11-1-06, 12-1-06....but formatted to
display as Nov-06...). Col.G has a formula to count entries per month (or
leave blank if no entries):

=IF((COUNTIF($A$4:$A$200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7))0,(COUNTIF($A$4:$A$ 200,"="&F6))-(COUNTIF($A$4:$A$200,"="&F7)),"")

This works OK. Col.H will be "total correct" per month, using the yes or no
in Col.D to get a total for that month which are correct (a "yes" entry in
col.D). I've been trying to combine COUNTIF and AND, but no luck. Any
ideas? THANKS!!