Got something to say or just want fewer pesky ads? Join us... 😊

[Technology] Excel help



Gwylan

Well-known member
Jul 5, 2003
31,323
Uffern
Bizarre, I posted this earlier and it didn't appear.

Here goes again. I'm trying to do something in Excel and the help pages don't tell me what I want. I have a column of salaries and I want to order them £1 to £24,999, £25,000 to £34,999, £35,000 to £45,000 etc right up to £100k +

Say I have 500 salaries, I want to know how many fit in each category. How do I go about doing this?

Any Excel expert able to help?
 




Shropshire Seagull

Well-known member
Nov 5, 2004
8,477
Telford
Use countifs

=countifs(a1:a500,">=25000"',"<=34999.99")

This would work fine but if you wanted more control over the groupings, rather than "hard-code" the values into the =COUNTIFS() formula you could type in the groupings value and then reference those cells in your formula

A B C
1 Lower Upper
2 0.00 24,999.99 =COUNTIFS(A8:A508,"=>"A2,"=<"B2)
3 25,000 49,999.00 =COUNTIFS(A8:A508,"=>"A3,"=<"B3)
4
5
6
7
8 <sal 1>
9 <sal 2>
etc ...

To make the formula robust to copy down, I'd also use F4 when creating the range to make it $A$8:$A$508

Also note that =COUNTIFS() needs the comparison operators to be wrapped in quotes
 


Gwylan

Well-known member
Jul 5, 2003
31,323
Uffern
Use countifs

=countifs(a1:a500,">=25000"',"<=34999.99")

Thanks that looks like what I want but I've tried different versions of this and I keep getting error messages. Do I have to name the columns first? and is there a =SUM in the formula somewhere?
 


akipling

Active member
Jan 12, 2010
163
Morecambe
This would work fine but if you wanted more control over the groupings, rather than "hard-code" the values into the =COUNTIFS() formula you could type in the groupings value and then reference those cells in your formula

A B C
1 Lower Upper
2 0.00 24,999.99 =COUNTIFS(A8:A508,"=>"A2,"=<"B2)
3 25,000 49,999.00 =COUNTIFS(A8:A508,"=>"A3,"=<"B3)
4
5
6
7
8 <sal 1>
9 <sal 2>
etc ...

To make the formula robust to copy down, I'd also use F4 when creating the range to make it $A$8:$A$508

Also note that =COUNTIFS() needs the comparison operators to be wrapped in quotes

Yeah this and then to be extra fancy use sumifs along the same lines to give you the total values of those in that range!

And stick some fancy borders and shadings on to make it look good! :lol:
 




Bry Nylon

Test your smoke alarm
Helpful Moderator
Jul 21, 2003
19,781
Playing snooker
Bizarre, I posted this earlier and it didn't appear.

Here goes again. I'm trying to do something in Excel and the help pages don't tell me what I want. I have a column of salaries and I want to order them £1 to £24,999, £25,000 to £34,999, £35,000 to £45,000 etc right up to £100k +

Who's earning £1 pa?

Are you Mike Ashley?
 




akipling

Active member
Jan 12, 2010
163
Morecambe
Thanks that looks like what I want but I've tried different versions of this and I keep getting error messages. Do I have to name the columns first? and is there a =SUM in the formula somewhere?

It could be a couple of things...

Is your salary data formatted as text? If so change it to number
Are u using an old version of excel? If so then change the formula to...

=count(a1:a500,">=25000")-countif(a1:a500,">=34999.99")

Obviously try and use the ranges as SS suggests above if you feel confident enough
 




Gwylan

Well-known member
Jul 5, 2003
31,323
Uffern
It could be a couple of things...

Is your salary data formatted as text? If so change it to number
Are u using an old version of excel? If so then change the formula to...

=count(a1:a500,">=25000")-countif(a1:a500,">=34999.99")

Obviously try and use the ranges as SS suggests above if you feel confident enough

I don't get an error message with that but nothing happens

I'm not confident at all, I hardly use Excel and when I do it's for simple budgeting or working out cricket averages! This is new to me
 


Gwylan

Well-known member
Jul 5, 2003
31,323
Uffern
This would work fine but if you wanted more control over the groupings, rather than "hard-code" the values into the =COUNTIFS() formula you could type in the groupings value and then reference those cells in your formula

A B C
1 Lower Upper
2 0.00 24,999.99 =COUNTIFS(A8:A508,"=>"A2,"=<"B2)
3 25,000 49,999.00 =COUNTIFS(A8:A508,"=>"A3,"=<"B3)
4
5
6
7
8 <sal 1>
9 <sal 2>
etc ...

To make the formula robust to copy down, I'd also use F4 when creating the range to make it $A$8:$A$508

Also note that =COUNTIFS() needs the comparison operators to be wrapped in quotes

Where does the ABC go in this?
 


akipling

Active member
Jan 12, 2010
163
Morecambe
Sorry I meant the formula to read

=countif(a1:a500,">=25000")-countif(a1:a500,">=34999.99")

With this I am assuming that your salary data is contained within range a1:a500

The ABC are the column headings
 



Paying the bills

Latest Discussions

Paying the bills

Paying the bills

Paying the bills

Albion and Premier League latest from Sky Sports


Top
Link Here