Formula Help based around countif and counta

[Mark McDonough]

I have a situation where there are 4 columns of rates from different
suppliers. The suppliers have only submitted rates for the locations they
can do and these rates are listed by state. Those locations where they
cannot provide the service have a zero in their field for those locations.
The zeros have to remain to show that that they are zero and not just
forgotten.

What I need to do is to count the list of rates only for non-zero rates and
do this by state. I know I could autofilter the data and count from that
but I'm looking for something a little bit more sophisticated than that. The
countif and counta functions should be involved here and exactly what is the
difference between these two.

Any help appreciated.

[Don Guillett]

sumproduct is your friend. Ideas to modify to your needs
to count
=sumproduct((a2:a22="joe")*(b2:b22=1))
to sum
=sumproduct((a2:a22="joe")*(b2:b22=1)*c2:c22)
--
Don Guillett
SalesAid Software
[email protected]
"Mark McDonough" <[email protected]> wrote in message
news:[email protected]...
>I have a situation where there are 4 columns of rates from different
>suppliers. The suppliers have only submitted rates for the locations they
>can do and these rates are listed by state. Those locations where they
>cannot provide the service have a zero in their field for those locations.
>The zeros have to remain to show that that they are zero and not just
>forgotten.
>
> What I need to do is to count the list of rates only for non-zero rates
> and do this by state. I know I could autofilter the data and count from
> that but I'm looking for something a little bit more sophisticated than
> that. The countif and counta functions should be involved here and exactly
> what is the difference between these two.
>
> Any help appreciated.
>
>

[Ron Coderre]

Try something like this:

Assuming you have a data structure like this:

StateList Supplier1 Supplier2 Supplier3 Supplier4
State1 1 0 3 1
State2 0 2 3 1
State3 1 0 0 1

The count of Supplier1 non-zero rates would be:
=COUNTIF(B:B,">0")

Supplier2 would be: =COUNTIF(C:C,">0")
etc

--------------
The COUNTIF functions counts items that match a user defined critera.
The COUNTA function counts non-blank cells....check Excel Help for more
information on what that means.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Mark McDonough" wrote:

> I have a situation where there are 4 columns of rates from different
> suppliers. The suppliers have only submitted rates for the locations they
> can do and these rates are listed by state. Those locations where they
> cannot provide the service have a zero in their field for those locations.
> The zeros have to remain to show that that they are zero and not just
> forgotten.
>
> What I need to do is to count the list of rates only for non-zero rates and
> do this by state. I know I could autofilter the data and count from that
> but I'm looking for something a little bit more sophisticated than that. The
> countif and counta functions should be involved here and exactly what is the
> difference between these two.
>
> Any help appreciated.
>
>
>