COUNTIF more than 1 criteria

[LottieCampbell]

Hi,

I am trying to get Excel to count the number of records in my spreadsheet with 3 separate criteria across 3 different columns.
For example I have a list of Inspections and I want Excel to tell me the number of Inspections that were created on a specific date, that are a certain type and are at a certain status. I have the columns with the headers 'Type' (which I want to = PreLim) Date (which I want to = 1011) and Status (which I want to = Completed).

I can do a COUNTIF function successfully but this only gives me the count of 1 criteria.

I do not want to have to create additional columns.

Any help would be much appreciated.

Thanks

[BarryTSL]

Use the CountifS formula

[LottieCampbell]

Thanks for the quick response Barry - I've tried that (=COUNTIFS(J1:J2918,"Prelim",AA1:AA2919,"1011PORT",AD1:AD2919,"Completed") - and I get an error message returned. Any idea why?

Thanks

[ATLGator]

It looks like your ranges are not the same: J1:J2918, AA1:AA2919, AD1:AD2919

Try fixing that to see if the error goes away.

[LottieCampbell]

Thanks Barry - it works!

[LottieCampbell]

Hi Barry - Can I incorporate an IF statement into that function, I want it to count the number of items where the City = 1011PORTS if the City is specified as Portsmouth?

[daddylonglegs]

Just those 2 conditions? You could use another COUNTIFS function with just those 2 - is the City a separate column?

[LottieCampbell]

No I have a list of ID numbers (for inspections) and columns headers City, Tenancy Year, Type, Status

eg.

Count of CompletedPreLims
1 Portsmouth 1011PORT Prelim Completed 1
2 Birmingham 1011BIRM Prelim Completed (etc..) 3

I want Excel to populate a new column against each record (shown as an example above - Count of CompletedPreLims) that counts it if the Type=Prelim, the Status = completed and IF the City = Portsmouth I want it to count it with all the other Portsmouth Inspections, if the city =Birmingham then I want it to count it with all the other Birmignahm Inspections and so forth (I only have 4 Cities to specify)

Hope that makes sense,

Thanks

[daddylonglegs]

OK, perhpas I'm missing something but wouldn't that be COUNTIFS again? For example if cities are in column Z

=COUNTIFS(J1:J2919,"Prelim",Z1:Z2919,"Portsmouth",AD1:AD2919,"Completed")

or are you looking at a formula that copies down and gives a cumulative total?

[LottieCampbell]

I want it to automatically change the formula based on the City Column. So if the first inspection (row 1) is for Portsmouth, I want it to populate the new cell in row 1 with a count of all the Completed Pre Lim Inspections in Portsmouth (for example 34). If the second inspection (row 2) is for Birmingham I want it to populare the new cell in row 2 with a count of all the completed pre lim inspections for Birmingham.. but I want to be able to copy the same formula all the way down and for it to work it out itself...

Hope that makes sense?

Thanks

[daddylonglegs]

OK try this in row 1 copied down

=COUNTIFS(J$1:J$2919,"Prelim",Z$1:Z$2919,Z1,AD$1:AD$2919,"Completed")

[LottieCampbell]

Thanks Victoria, but no that gives me the same result for every cell - not city specific. Nightmare!

[daddylonglegs]

As you copy the formula down the z1 will change to Z2, Z3 etc. so you should get the count for the City in each row with that City - again I assumed that City is in column Z, which formula did you use?

[LottieCampbell]

Yes Sorry - that works now. Thanks for your help.