CountIFS across multiple columns and multiple sheets within specified date range

Hi everyone,

I'm needing to count the total number of cells that say "Housing" within G3:G16 through K3:16 and across 6 different sheets (all use the same column and rows), within a two week period (3/7/14-3/21/14).

I'm trying to find an easier way than what's below to input this formula (I put a key at the bottom explaining what some of the information is). Basically, what's below is a separate countif statement for each column involved and I simply add them all together. It does work but I'm wondering if there is a simpler and cleaner formula I could use instead.

=COUNTIFS(Table4[Category 1],"Housing",Table4[Intake],">="&'final sheet'!AL2,Table4[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table4[Category 2],"Housing",Table4[Intake],">="&'final sheet'!AL2,Table4[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table4[Category 3],"Housing",Table4[Intake],">="&'final sheet'!AL2,Table4[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table4[Category 4],"Housing",Table4[Intake],">="&'final sheet'!AL2,Table4[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table4[Category 5],"Housing",Table4[Intake],">="&'final sheet'!AL2,Table4[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table446[Category 1],"Housing",Table446[Intake],">="&'final sheet'!AL2,Table446[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table446[Category 2],"Housing",Table446[Intake],">="&'final sheet'!AL2,Table446[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table446[Category 3],"Housing",Table446[Intake],">="&'final sheet'!AL2,Table446[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table446[Category 4],"Housing",Table446[Intake],">="&'final sheet'!AL2,Table446[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table446[Category 5],"Housing",Table446[Intake],">="&'final sheet'!AL2,Table446[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table447[Category 1],"Housing",Table447[Intake],">="&'final sheet'!AL2,Table447[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table447[Category 2],"Housing",Table447[Intake],">="&'final sheet'!AL2,Table447[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table447[Category 3],"Housing",Table447[Intake],">="&'final sheet'!AL2,Table447[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table447[Category 4],"Housing",Table447[Intake],">="&'final sheet'!AL2,Table447[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table447[Category 5],"Housing",Table447[Intake],">="&'final sheet'!AL2,Table447[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table448[Category 1],"Housing",Table448[Intake],">="&'final sheet'!AL2,Table448[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table448[Category 2],"Housing",Table448[Intake],">="&'final sheet'!AL2,Table448[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table448[Category 3],"Housing",Table448[Intake],">="&'final sheet'!AL2,Table448[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table448[Category 4],"Housing",Table448[Intake],">="&'final sheet'!AL2,Table448[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table448[Category 5],"Housing",Table448[Intake],">="&'final sheet'!AL2,Table448[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table449[Category 1],"Housing",Table449[Intake],">="&'final sheet'!AL2,Table449[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table449[Category 2],"Housing",Table449[Intake],">="&'final sheet'!AL2,Table449[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table449[Category 3],"Housing",Table449[Intake],">="&'final sheet'!AL2,Table449[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table449[Category 4],"Housing",Table449[Intake],">="&'final sheet'!AL2,Table449[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table449[Category 5],"Housing",Table449[Intake],">="&'final sheet'!AL2,Table449[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table450[Category 1],"Housing",Table450[Intake],">="&'final sheet'!AL2,Table450[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table450[Category 2],"Housing",Table450[Intake],">="&'final sheet'!AL2,Table450[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table450[Category 3],"Housing",Table450[Intake],">="&'final sheet'!AL2,Table450[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table450[Category 4],"Housing",Table450[Intake],">="&'final sheet'!AL2,Table450[Intake],"<="&'final sheet'!AO2)+COUNTIFS(Table450[Category 5],"Housing",Table450[Intake],">="&'final sheet'!AL2,Table450[Intake],"<="&'final sheet'!AO2)

AL2 contains Early Date
AO2 contains Later Date
Intake contains list of Dates

Category 1 = G3:G16
Category 2 = H3:H16
Category 3 = I3:I16
Category 4 = J3:J16
Category 5 = K3:K16

Table4 = Sheet 3
Table446 = Sheet 4
Table447 = Sheet 5
Table448 = Sheet 6
Table449 = Sheet 7
Table450 = Sheet 8

COUNTIFS doesn't work very well over different sheets, so you have to compile lengthy formulae like that to get around this. One alternative is to have your COUNTIF formula within each sheet, so that it only acts upon the data within that sheet. Suppose you put this (reduced) formula in cell X1 of every subsidiary sheet - then you could have a formula like this in your summary sheet:

=SUM(first:last!X1)

to add them all together, where first and last are the names of the sheets which surround the range of sheets that you want to sum (the summary sheet should be outside that range).

Hope this helps.

Pete

Thanks Pete! This does help!