Excel Formula

  • Thread starter Thread starter Glenn Robertson
  • Start date Start date
G

Glenn Robertson

Hi, hope someone can help.

I have a spreadsheet that has loads of rows of
information. Column B has an amount in £ varying from £1
to £100,000. I need a formula or macro that will look in
that column and tell me how many rows are under £500 then
how many rows are between £500 and £1000 and how many rows
between £1000 and £5000 etc.

Thanks
Glenn
 
Hi Glenn

one possible solution:
=SUMPRODUCT(B1:B999<500) or as an alternativ =COUNTIF(B1:B999,"<500")
=SUMPRODUCT((B1:B999<1000)*(B1:B999>=500)) or
=COUNTIF(B1:B999,"<1000")-COUNTIF(B1:B999,"<500")
and so on

Frank
 
Glenn,

Assume the results go in C1, etc

C1: =COUNTIF(B:B,"<500")
C2: =COUNTIF(B:B,"<1000") - C1
etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi, hope someone can help.

I have a spreadsheet that has loads of rows of
information. Column B has an amount in £ varying from £1
to £100,000. I need a formula or macro that will look in
that column and tell me how many rows are under £500 then
how many rows are between £500 and £1000 and how many rows
between £1000 and £5000 etc.

Thanks
Glenn
 
Thanks that's excellent but what about if i want it to say
over £500 but under £2000 !!!

Thanks
Glenn
 
Back
Top