Count If across rows

[Guest]

I read the postings on how to use a sum function to perform soemthing similar
to CountIf in excel. However, I am trying to perform a countif like function
accross rows, not down columns. To be specific, for each record, I have 5
fields, each with a change in volume for a specific month (5 Columns: July-
August). I want to take a tally- for each record, I need to know how many of
these change in volumes are positive. Essentially, i would want to create a
new colume with a count for each record, based on previous fields. Any ideas?
Thanks in advance.
Joel

 

[Wayne Morgan]

You can add "columns" in a row by adding a calculated field or control that
will add up the other fields. To count how many of these other fields have
positive values you need to add 1 instead of the value of the field whenever
the field contains a positive number.

=Abs(Nz([Field1]>0,0) + Nz([Field2]>0,0) + Nz([Field3]>0,0) +
Nz([Field4]>0,0) + Nz([Field5]>0,0))

should give you this count. It relies on the fact that VBA/Access will treat
True as -1. So, if the value in the field is greater than zero, -1 will be
added. Use Abs() to get the positive value. The Nz() is to add zero if the
value is Null; otherwise, if any of the values is Null, the result will be
Null.

 

[Guest]

Should not that be <0 ?

You can add "columns" in a row by adding a calculated field or control that
will add up the other fields. To count how many of these other fields have
positive values you need to add 1 instead of the value of the field whenever
the field contains a positive number.

=Abs(Nz([Field1]>0,0) + Nz([Field2]>0,0) + Nz([Field3]>0,0) +
Nz([Field4]>0,0) + Nz([Field5]>0,0))

should give you this count. It relies on the fact that VBA/Access will treat
True as -1. So, if the value in the field is greater than zero, -1 will be
added. Use Abs() to get the positive value. The Nz() is to add zero if the
value is Null; otherwise, if any of the values is Null, the result will be
Null.

--
Wayne Morgan
MS Access MVP

I read the postings on how to use a sum function to perform soemthing
similar
to CountIf in excel. However, I am trying to perform a countif like
function
accross rows, not down columns. To be specific, for each record, I have 5
fields, each with a change in volume for a specific month (5 Columns:
July-
August). I want to take a tally- for each record, I need to know how many
of
these change in volumes are positive. Essentially, i would want to create
a
new colume with a count for each record, based on previous fields. Any
ideas?
Thanks in advance.
Joel

 

[Wayne Morgan]

No, he wants to know how many have positive values so True needs to be
returned if the value is positive. Positive numbers are >0 (greater than
zero).

?1>0
True

 

[Guest]

Your expression is taking the absolute value after evaluation.
The field contains either a zero or a minus one.
In my math minus one is less than one.
How am I going wrong?

 

[Wayne Morgan]

The fields, I called them Field1, Field2, etc, contain the "change in volume
for a specific month" mentioned by Joel. He said he wanted to get a count of
how many of these field (5 total) in each row contained a positive value for
this "change in volume". The inequality I test for is whether or not this
"change in volume" is greater than zero. If it is, then it is a positive
number and the inequality will return True. Access treats this True value
as -1 (and False as 0). I add up the -1's that are returned, which will give
a negative count. I then take the Abs() to change it to a positive count.

=Abs(Nz([Field1]>0,0) + Nz([Field2]>0,0) + Nz([Field3]>0,0) +
Nz([Field4]>0,0) + Nz([Field5]>0,0))

In the example above, if fields Field1 and Field5 have positive values and
the other three are 0, negative values, or Null, then the equation will be

=Abs(-1 + 0 + 0 + 0 + -1) for a result of 2. There were 2 fields that had
positive values in them.

Positive number > 0 = True (-1)
0 or Neg number > 0 = False (0)
Null > 0 = Null, but Nz() will change this to 0.

As a test from the Immediate window, putting in numbers or Null for the
field names:

?Abs(Nz(23>0,0) + Nz(Null>0,0) + Nz(-4>0,0) + Nz(0>0,0) + Nz(7>0,0))
2

 

[Guest]

You are correct. I went back and read all the thread. Somewhere along the
way I got it in my mind that the fields were checkboxes that were being
counted.

The fields, I called them Field1, Field2, etc, contain the "change in volume
for a specific month" mentioned by Joel. He said he wanted to get a count of
how many of these field (5 total) in each row contained a positive value for
this "change in volume". The inequality I test for is whether or not this
"change in volume" is greater than zero. If it is, then it is a positive
number and the inequality will return True. Access treats this True value
as -1 (and False as 0). I add up the -1's that are returned, which will give
a negative count. I then take the Abs() to change it to a positive count.

=Abs(Nz([Field1]>0,0) + Nz([Field2]>0,0) + Nz([Field3]>0,0) +
Nz([Field4]>0,0) + Nz([Field5]>0,0))

In the example above, if fields Field1 and Field5 have positive values and
the other three are 0, negative values, or Null, then the equation will be

=Abs(-1 + 0 + 0 + 0 + -1) for a result of 2. There were 2 fields that had
positive values in them.

Positive number > 0 = True (-1)
0 or Neg number > 0 = False (0)
Null > 0 = Null, but Nz() will change this to 0.

As a test from the Immediate window, putting in numbers or Null for the
field names:

?Abs(Nz(23>0,0) + Nz(Null>0,0) + Nz(-4>0,0) + Nz(0>0,0) + Nz(7>0,0))
2

--
Wayne Morgan
MS Access MVP

Your expression is taking the absolute value after evaluation.
The field contains either a zero or a minus one.
In my math minus one is less than one.
How am I going wrong?

 

[Wayne Morgan]

I went back and read all the thread. Somewhere along the

way I got it in my mind that the fields were checkboxes that were being
counted.

Been there, done that.