Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif 0, <8 | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |