Countif problem

  • Thread starter Thread starter Jeff Armstrong
  • Start date Start date
J

Jeff Armstrong

I am trying to use the countif function to do some simple
logical addition. My problem is I need to count the
number of rows based on two different criteria's. For
example, column A has the name of a device. Column G has
the name of different components for a given device. I
need to add the number of rows based on the device and a
particular component for that device. I believe I need to
add the AND statement in my formula, but I can't seem to
get the syntax right. So far my formula looks like this:

=COUNTIF($G$2:$G$217,G2)

This counts all the rows for all devices for the component
in cell G2. Now all I need is for it to only count for a
particular device that I choose in column A. Can someone
please show me how to correctly add an AND clause in a
countif function.

Thanks,
Jeff
 
Jeff Armstrong said:
I am trying to use the countif function to do some simple
logical addition. My problem is I need to count the
number of rows based on two different criteria's. For
example, column A has the name of a device. Column G has
the name of different components for a given device. I
need to add the number of rows based on the device and a
particular component for that device. I believe I need to
add the AND statement in my formula, but I can't seem to
get the syntax right. So far my formula looks like this:

=COUNTIF($G$2:$G$217,G2)

This counts all the rows for all devices for the component
in cell G2. Now all I need is for it to only count for a
particular device that I choose in column A. Can someone
please show me how to correctly add an AND clause in a
countif function.

Thanks,
Jeff
Click to expand...

You can't do this in the way you suggest, as the first parameter in COUNTIF
must be a range, not a function. Instead, use SUMPRODUCT, like this:
=SUMPRODUCT((G2:G217=$G$2)*(A2:A217=device))
or
=SUMPRODUCT(--(G2:G217=$G$2),--(A2:A217=device))
where you should replace device with whatever you want to find in column A
(or a reference to a cell containing this).
 
-----Original Message-----


You can't do this in the way you suggest, as the first parameter in COUNTIF
must be a range, not a function. Instead, use SUMPRODUCT, like this:
=SUMPRODUCT((G2:G217=$G$2)*(A2:A217=device))
or
=SUMPRODUCT(--(G2:G217=$G$2),--(A2:A217=device))
where you should replace device with whatever you want to find in column A
(or a reference to a cell containing this).

Worked great. However, what if I wanted to add another
Click to expand...
criteria to narrow my search further. How would I add an
additional criteria to the existing formula?
 
-----Original Message-----
criteria to narrow my search further. How would I add an
additional criteria to the existing formula?
Click to expand...

Add as many as you like, like this:
=SUMPRODUCT((G2:G217=$G$2)*(A2:A217=device)*(B2:B217=criterion1)*(M2:M217=cr
iterion2))
 
Back
Top