Countif

  • Thread starter Thread starter Sara Mellen
  • Start date Start date
S

Sara Mellen

This is from an Access non-expert--no sql background and little vba.

Is there an equivalent Access expression to Excel's "countif" function?
This could be done in a query or as a calculated control on a report.

This field has one of four values appearing in it: 1, 2, 3 or 4. I need to
count how many times each value appears and report them. So for example, if
the number "4" appears in 10 records, the formula would report the count of
"10".

The second part of this is that I need to then do a percent of each value.
In other words, if there are 100 answers, including 2 4s, 2 3s, 3 2s and 3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara
 
To count the number of times that 4 appears, use a text box in a group or
report header or footer like:
=Sum( Abs( [YourField] = 4) )
Part 2:
=Sum( Abs( [YourField] = 4) ) / Count([YourField])
or
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /
Count([YourField]), 0)
 
Thanks so much for your help, Duane. The fields work great! I'm having
trouble with the grouping, though--I only need summary data, no detail.
Please see my reply to Ted if you have time to give me your "two cents".

Thanks again.

Sara

Duane Hookom said:
To count the number of times that 4 appears, use a text box in a group or
report header or footer like:
=Sum( Abs( [YourField] = 4) )
Part 2:
=Sum( Abs( [YourField] = 4) ) / Count([YourField])
or
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /
Count([YourField]), 0)

--
Duane Hookom
MS Access MVP
--

Sara Mellen said:
This is from an Access non-expert--no sql background and little vba.

Is there an equivalent Access expression to Excel's "countif" function?
This could be done in a query or as a calculated control on a report.

This field has one of four values appearing in it: 1, 2, 3 or 4. I
Click to expand...
need
to
count how many times each value appears and report them. So for
Click to expand...
example,
if
the number "4" appears in 10 records, the formula would report the
Click to expand...
count
of
"10".

The second part of this is that I need to then do a percent of each value.
In other words, if there are 100 answers, including 2 4s, 2 3s, 3 2s and 3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara
Click to expand...
Click to expand...
 
Glad to be of assistance. I don't see a another thread from you or Ted.

--
Duane Hookom
MS Access MVP


Sara Mellen said:
Thanks so much for your help, Duane. The fields work great! I'm having
trouble with the grouping, though--I only need summary data, no detail.
Please see my reply to Ted if you have time to give me your "two cents".

Thanks again.

Sara

Duane Hookom said:
To count the number of times that 4 appears, use a text box in a group or
report header or footer like:
=Sum( Abs( [YourField] = 4) )
Part 2:
=Sum( Abs( [YourField] = 4) ) / Count([YourField])
or
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /
Count([YourField]), 0)

--
Duane Hookom
MS Access MVP
--

Sara Mellen said:
This is from an Access non-expert--no sql background and little vba.

Is there an equivalent Access expression to Excel's "countif" function?
This could be done in a query or as a calculated control on a report.

This field has one of four values appearing in it: 1, 2, 3 or 4. I
Click to expand...
need
to
count how many times each value appears and report them. So for
Click to expand...
example,
if
the number "4" appears in 10 records, the formula would report the
Click to expand...
count
of
"10".

The second part of this is that I need to then do a percent of each value.
In other words, if there are 100 answers, including 2 4s, 2 3s, 3 2s
Click to expand...
Click to expand...
and
3
Click to expand...
Click to expand...
 
Sara,
I found the other thread in queries and responded there.

--
Duane Hookom
MS Access MVP


Duane Hookom said:
Glad to be of assistance. I don't see a another thread from you or Ted.

--
Duane Hookom
MS Access MVP


Sara Mellen said:
Thanks so much for your help, Duane. The fields work great! I'm having
trouble with the grouping, though--I only need summary data, no detail.
Please see my reply to Ted if you have time to give me your "two cents".

Thanks again.

Sara

Duane Hookom said:
To count the number of times that 4 appears, use a text box in a group or
report header or footer like:
=Sum( Abs( [YourField] = 4) )
Part 2:
=Sum( Abs( [YourField] = 4) ) / Count([YourField])
or
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /
Count([YourField]), 0)

--
Duane Hookom
MS Access MVP
--

This is from an Access non-expert--no sql background and little vba.

Is there an equivalent Access expression to Excel's "countif" function?
This could be done in a query or as a calculated control on a report.

This field has one of four values appearing in it: 1, 2, 3 or 4. I need
to
count how many times each value appears and report them. So for example,
if
the number "4" appears in 10 records, the formula would report the count
of
"10".

The second part of this is that I need to then do a percent of each value.
In other words, if there are 100 answers, including 2 4s, 2 3s, 3 2s
Click to expand...
and
3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara
Click to expand...
Click to expand...
Click to expand...
 
Back
Top