Formula required to count number of records

[robertguy]

Hi,

what I have is a worksheet with a 'Business Group' column and an 'Observation' column

The 'Business Group' column can have data for five diffrent offices i.e.

East Office
Head Office
North Office
South Office
West Office

and the 'Observation' column having data i.e.

F= Fail
O = Out of period
P = Pass
R = Risk"

What I'm trying to do is to automatically calulate the totals for each 'Business Group' and 'Observation' in the summary tabel D1:I7 possible via sumif or vlookups etc

Office Fail = F Out of Period = 0 Pass = P Risk = R Total
East Office 2 2 4 4 12


I have attached an example file to demonstate my requirements

Many thanks in advance - Excel version 2007


Regards

Rob

[daddylonglegs]

Hello Rob,

If you change the headers in E1:H1 to show just the single letter that matches your data, i.e. F, O, P and R then you can put this formula in E2 and copy it across and down the whole table

=COUNTIFS($B:$B,$D2,$C:$C,E$1)

[amotto11]

youll want to use a countifs function, your first one (east office, F) would be countifs($B$12:$B$89,$D1,$C$12:$C$89,"F") you can drag down and over from there and just change "F" to your other letter. let nme know how this works for you

[Søren Larsen]

If you changed your headings to just F, O, P and R, then you can use this:

Please Login or Register  to view this content.

Else you can use this, but you need to change the 0 (zero) in F1 to an O:

Please Login or Register  to view this content.

For both formulas you'd want to put them in E2 and copy down and across.

[daddylonglegs]

Note: you could still use COUNTIFS even with the headers unchanged (except 0 to O), i.e.

=COUNTIFS($B:$B,$D2,$C:$C,RIGHT(E$1))

[Søren Larsen]

Ahh, thanks for pointing that out daddylonglegs! In that case my second formula is superflous (unless you're using a version of Excel older than 2007 of course).

[robertguy]

Guys,

many thanks for all your responses they are very much appreciated.

I've just noticed that in Column C if have input O and in F1 I have put a 0 (zero) when in fact F1 should have been a O.

That said. I have tried all the formulas in E2 but with the exception of Søren's second formula they all return #NAME?, however, Søren's second formula works as required.

I'm not sure why #NAME? is being displayed but would be grateful if you guys could amend and re-load my example.xls to see where I’m going wrong

Once again many thanks to one all for your responses

Best Regards


Rob

[Søren Larsen]

The reason why you get the error is probably that you are using a version of Excel older than 2007, and thus =COUNTIFS() (and others) is unavailable to you.

[daddylonglegs]

but.....from post 1

Excel version 2007

[robertguy]

Søren,

I've just realised I used my works PC which is running 2007 to create and post my Example file and when i got home I downloaded my Example file and tested the formulas on my home PC which is running 2003

Rob