# Boards

## Excel help!

I've got a spreadsheet and one of the columns tells me the number of days people have stayed in accommodation. I want to do a count of the total people who have stayed

0-6 days

7-27 days

28+ days

How do I do it?

**Big Lad**-*Pro Rock*- The Fangasm: Kid A by
**Radiohead** **The KVB**-*Only Now Forever*- "The rules have changed, that's why I’m not following them": DiS Meets
**Youngr** - How To Organise A Music Festival #19:
**Ritual Union** **Richard Ashcroft**-*Natural Rebel***Adrianne Lenker**-*abysskiss*- "Making a secret band is quite strange when you think about it": DiS Meets
**Lights On Moscow**

I've got a spreadsheet and one of the columns tells me the number of days people have stayed in accommodation. I want to do a count of the total people who have stayed

0-6 days

7-27 days

28+ days

How do I do it?

## =countif

## where A:A is your range

=COUNTIF(A:A,")

## fucking DIS

cant use a less then for some reason

=countif(A:A , " < 7 " )

=countif(A:A , " > 28 " )

=count(A:A) - (cell with first countif + cell with second countif)

## i'm trying

=countif(A1:A100s not working. Where am I going wrong?

## ffs where's my less than?!

= countif(A1:A100 LESSTHAN 7)

## yeah

it's happening to me to. dis hates excel now apparently

## ah I needed quotation marks

## insert a pivot table

drag & drop number of days into the row title box and the count box as well.

## ^

What I'd do.

## Some kind of COUNTIF with the number range?

Been a while since I excelled.

## use your fingers

Then toes, if need be.

=COUNTIF(A1:A100,"28")

where A1:100 is the range that contain the numbers

for the 7-27 do

=count(a1:a100)- B1 - B2

where b1 and b2 are the cells containing the first two

## appear?

## that'#s only half of it

=COUNTIF(A1:A100,"28")

where A1:100 is the range that contain the numbers

for the 7-27 do

=count(a1:a100)- B1 - B2

where b1 and b2 are the cells containing the first two

## wtf?!

i think those = are making my post go weird

Countifs(a;a,">="&0,a;a"&6)

## *

=countIfs(a:a,">="&0,a:a,"&6)

## I would just select all the data and then do DATA>SORT>

then sort it by that column and just count them using an abacus

=first line of test post

=second line of test post

## use countif

i don't know what the fuck is going on with my posts up there

## Are you in an version of excel which is >=Excel 2007?

If so, CountifS gives you a bit more flexibility than countif to to < conditions

Otherwise, create additional columns to return TRUE/FALSE for each of the buckets you want, then do countif on those columns with TRUE as the condition you're looking for. e.g. First column would be: =AND(A1>=0, A1<=6)

=countifs(a:a, ">"&6,a:a,"&27)

## it's cos 'less than' and 'greater than' symbols could be html

so sean just deletes them to avoid htmhell days

## that was a great day

## well now our rich and wonderful excel threads are fucked

sean!!! sort this shit out

whenever I type the formula part of it disappears

COUNTIFS(criteria range1, criteria1, criteria range2, criteria2)

criteria range 1 and 2 are the same range with your values in

criteria 1 is the minimum value you want to count, in excel you have to put >= in quote marks followed by an & and then the number, then for criteria 2 your max put <= in quotes followed by & and the max number

## Solved - thanks everyone!

## bd

## managed to get hold of an abacus then

## really simple thing that's a bit of a blind spot for me

Say in A1 i have a number A and in B1 i have another number, B

What formula do i need to put in C1 if i want it to display

"A is (A1-B1) bigger than B" (where in A1-B1 is displayed as a figure formatted in my choosing) IF a is bigger than B, or "B is (B1-A1) bigger than A" (where B1-A1 is displayed as a figure formatted in my choosing) or, in the unlikely event that A1=B1, "Jesus, what a coincidence! A and B are the same!"

?

is this even possible to do with just one cell?

## yeah

give me a sec and I'll type it out

## .

=IF(A1>B1,"A is "&A1-B1&" bigger than B",IF(A1))

## oh for fucks sake DiS, swallowed half my formula

=if(a1>b1,''A is''&A1-B1&'' bigger than B'',if(a1))

dis are the rest

## *ate

if(a1jesus etc)) in the bit where if(a1)) is now

## I've PMed it to you

I think me and ThingsThatFly were on the same lines though

## that's ALMOST sorted thanks guys

ccb dm'd me, minus linebreaks

=IF(A1>B1,

"A is "&

A1-B1&

" bigger than B",IF

(A1

s giving A1-B1 to 15 decimal places but i want it to be to two decimal places.

## wow what a carcrash

please ignore the above. ccb pm'd me a working solution, but it gives the numerical values to 15 decimal places, but i'd like 2 decimal places. trying to numerically format the cell C1 hasnt helped - any solution to this?`

## use TEXT function around A1-B1

if you incorporate the round function where you have a1-b1 and b1-a1 it should work so (round,a1-b1,2) in its place

## do =ROUND( that massive formula),2

## actually, that's not quite correct

I'll PM you again

## YESSSSSSS great stuff thanks

## this was the ccb solution, for posterity

http://i.imgur.com/brHq3sG.jpg