COUNTIFS w/ TODAY() Function Help

Question

I have a spreadsheet for tracking flights flown and cancelled for an aircraft.  I have columns for each day from 1 January 2016 to 31 Dec.  And for the Rows I have a row for Flown, Cancelled due to Weather, and cancelled due to Maintenance.  I use 'X' to check each cell for flown or cancelled.  On a separate sheet, I have stats for each month and I use the COUNTIF function to calculate the 'X' for each row on the other sheet for each month.  What I'm having problem with is a way to calculate flights flown for the last 7 or 30 days. 

Answer 1

Hello OssieMac,

Thank you very much. The formula worked great and yes they were two separate sheets.

Regards,

Ernie_G

Answer 2

It appears that the Last 7 days, last 30 days etc is on another worksheet. I don't understand the 2 columns for each header. However, in cell B17 (Under first column of "Last 7 days") the following formula where "Sheet1" is the sheet depicted with the dates for the year and the "X"'s.

=COUNTIFS(Sheet1!$B$2:$NC$2,">="&TODAY()-7,Sheet1!$B5:$NC5,"=X")

Note the absolute referencing (with $ signs) for the first part of the formula for both column Id and row Id. For the second part of the formula only the column Id has the $ sign.

You should be able to copy the formula across in row 17 and then edit the formulas in each column and change the number of days to subtract from Today(). You can then select all of the formulas across row 17 and copy down.