Function Statements

[Carolyn]

How can I write a function statement to calculate a range of informatin. IE., a list of populations for cities, and I need to know how many cities have a population between 800,000 and 1,000,000?

 

[Frank Kabel]

Hi Carolyn
a little bit more information would be helpful. Bu assuming that in
your worksheet column A stores city names and column B the respective
pupulation tray the following formula:
=SUMPRODUCT((B1:B1000>=1000000)*(B1:B1000>=800000))

Frank

 

[Ken Wright]

=COUNTIF(Range,">=800000")-COUNTIF(Range,">1000000")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

How can I write a function statement to calculate a range of informatin. IE.,

a list of populations for cities, and I need to know how many cities have a
population between 800,000 and 1,000,000?

 

[Norman Harker]

Hi Carolyn!

With the populations in column A

One way using COUNTIF:
=COUNTIF(A1:A10,">=800000")-COUNTIF(A1:A10,">1000000")



Or another using implicit if statement structures in SUMPRODUCT

=SUMPRODUCT((A1:A10>=800000)*(A1:A10<=1000000))



Or you may prefer the explicit IF function approach that uses SUM



=SUM(IF(A1:A10>=800000,1,0)*IF(A1:A10<=1000000,1,0))

Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.



Appears as:



{=SUM(IF(A1:A10>=800000,1,0)*IF(A1:A10<=1000000,1,0))}


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

How can I write a function statement to calculate a range of

informatin. IE., a list of populations for cities, and I need to know
how many cities have a population between 800,000 and 1,000,000?

 

[Carolyn]

Thank you so much, this worked perfectly.