CountIF
 

I have a verticle list of names, 4 names to be exact but they repeat
over a series of about 500 rows down. In the column next to the names
are monetary values, however some cells are blank. I need to write a
formula that will count the values for each of the 4 people. Any
ideas?

I tried a few COUNTIF() but not sure. Can i throw an INDEX formula in
there??

Thanks for your help.

CountIF
 

On Thu, 30 Jul 2009 12:13:41 -0700 (PDT), Brian Morris
wrote:

I have a verticle list of names, 4 names to be exact but they repeat
over a series of about 500 rows down. In the column next to the names
are monetary values, however some cells are blank. I need to write a
formula that will count the values for each of the 4 people. Any
ideas?

I tried a few COUNTIF() but not sure. Can i throw an INDEX formula in
there??

Thanks for your help.


If you really mean count, then you should go for COUNTIF(), but then
you don't need the column with monetary values.
But if you intend to sum the monetary values per name, then you should
go for SUMPRODUCT(). Like this for the name "John Doe":

=SUMPRODUCT((A1:A500="John Doe")*(B1:B500))

Hope this helps / Lars-Åke

CountIF
 

On Jul 30, 3:22*pm, Lars-Åke Aspelin
wrote:
On Thu, 30 Jul 2009 12:13:41 -0700 (PDT), Brian Morris

wrote:
I have a verticle list of names, 4 names to be exact but they repeat
over a series of about 500 rows down. *In the column next to the names
are monetary values, however some cells are blank. *I need to write a
formula that will count the values for each of the 4 people. *Any
ideas?

I tried a few COUNTIF() but not sure. *Can i throw an INDEX formula in
there??

Thanks for your help.

If you really mean count, then you should go for COUNTIF(), but then
you don't need the column with monetary values.
But if you intend to sum the monetary values per name, then you should
go for SUMPRODUCT(). Like this for the name "John Doe":

=SUMPRODUCT((A1:A500="John Doe")*(B1:B500))

Hope this helps / Lars-Åke

I need to count the number of monetary values, not the amount, just
how many monetary values per person, using the two columns.
The sumproduct wouldnt work for that i dont think. I had someone
suggest an Index formula but I am not sure how to use that...

CountIF
 

On Thu, 30 Jul 2009 12:30:00 -0700 (PDT), Brian Morris
wrote:

On Jul 30, 3:22*pm, Lars-Åke Aspelin
wrote:
On Thu, 30 Jul 2009 12:13:41 -0700 (PDT), Brian Morris

wrote:
I have a verticle list of names, 4 names to be exact but they repeat
over a series of about 500 rows down. *In the column next to the names
are monetary values, however some cells are blank. *I need to write a
formula that will count the values for each of the 4 people. *Any
ideas?

I tried a few COUNTIF() but not sure. *Can i throw an INDEX formula in
there??

Thanks for your help.

If you really mean count, then you should go for COUNTIF(), but then
you don't need the column with monetary values.
But if you intend to sum the monetary values per name, then you should
go for SUMPRODUCT(). Like this for the name "John Doe":

=SUMPRODUCT((A1:A500="John Doe")*(B1:B500))

Hope this helps / Lars-Åke

I need to count the number of monetary values, not the amount, just
how many monetary values per person, using the two columns.
The sumproduct wouldnt work for that i dont think. I had someone
suggest an Index formula but I am not sure how to use that...


OK, then lets modify the formula:

=SUMPRODUCT((A1:A500="John Doe")*(B1:B500<""))

This will count the number of rows where there is "John Doe" in column
A and where where the column B is not blank.

Hope this helps / Lars-Åke