Using AND + COUNTIF to filter / count totals
 

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!!

Using AND + COUNTIF to filter / count totals
 

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!!

Using AND + COUNTIF to filter / count totals
 

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!!

Using AND + COUNTIF to filter / count totals
 

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!!

Using AND + COUNTIF to filter / count totals
 

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!!