Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Function Inquiry
I have two columns, Column A with States listed (i.e. FL, NY, AB) and Column
B with a numeric value. I need to know how many people live in NY that have a value in Column B greater than zero. Can anyone help? Thanks. coffeyt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Function Inquiry
I assume a VLOOKUP is what you're looking for. I would try this in the cell
you would like to return the data: =VLOOKUP("NY",A3:B5,2,FALSE) with "NY" being the state you would like to look up (you can reference another cell here if you like) "A3:B5" being the range of states and numeric values in columns A and B 2 being the column you would like returned in the range Hope this helps. Reply back if you need further info or if this is way off. -Chad "TrevorC" wrote: I have two columns, Column A with States listed (i.e. FL, NY, AB) and Column B with a numeric value. I need to know how many people live in NY that have a value in Column B greater than zero. Can anyone help? Thanks. coffeyt |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Function Inquiry
A few options.
1) SUMIF. in another cell, use =SUMIF(A:A,"NY",B:B) You can type "NY" in cell G4 and then do =SUMIF(A:A,G4,B:B) 2) Subtotals Sort it by state first. Then use data subtotals. At each change in State, use function SUM on (column with population). 3) pivot table http://peltiertech.com/Excel/Pivots/pivottables.htm -- Allllen "TrevorC" wrote: I have two columns, Column A with States listed (i.e. FL, NY, AB) and Column B with a numeric value. I need to know how many people live in NY that have a value in Column B greater than zero. Can anyone help? Thanks. coffeyt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Function Inquiry
Hi Trevor
One way =SUMPRODUCT((A1:A1000="NY")*(B1:B10000)) change range to suit but ensure they are of equal size. Place the value NY in a cell, e.g. C1 and use that in place of "NY". Then changing the value in C1 to FL or AB -- Regards Roger Govier "TrevorC" wrote in message ... I have two columns, Column A with States listed (i.e. FL, NY, AB) and Column B with a numeric value. I need to know how many people live in NY that have a value in Column B greater than zero. Can anyone help? Thanks. coffeyt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
the countA function is counting everything even empty cells | Excel Worksheet Functions | |||
Function - Counting Years in range | Excel Worksheet Functions | |||
Function Help - Counting Days | Excel Discussion (Misc queries) | |||
Pivot table count function not counting all data. | Excel Discussion (Misc queries) | |||
Date & Time | New Users to Excel |