CountIF

  • Thread starter Thread starter Brian Morris
  • Start date Start date
B

Brian Morris

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.
 
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.
Click to expand...


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
 
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
Click to expand...

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...
 
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...
Click to expand...


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
 
Back
Top