Counting Function Inquiry

[TrevorC]

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

 

[Chad]

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

 

[Allllen]

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

 

[Roger Govier]

Hi Trevor

One way
=SUMPRODUCT((A1:A1000="NY")*(B1:B1000>0))
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