QUESTION: Hi Tom,
I am wondering if there is a way to have formulas match up with filters. For example, on my reporting sheet I have 2 cells named "# of Minor Problem Reports in Reporting Period", and "# of Major Problem Reports in Reporting Period".. for each of these I have a cell that calculates these metrics from my data sheet. On my data sheet I have one column for Severity... This is where people enter if the problem is Major or Minor. On my report sheet where I am counting the # of Major Problem Reports and # of Minor Problem Reports I have COUNTIF statements in the cells so that it can count how many Minor and how many major problems there are.
What I need to do is to somehow figure out a way to have the report sheet calculate the # major's and minors on a monthly basis- not as a whole. For example I would want to see how many minor problem reports were recorded during December of 2012. Right now it is calculating all of the data that is entered under the Severity column.
I was thinking that it would be easy if I could go into the data sheet and filter the dates to which month I need to report and hopefully have only that filtered data be tracked back into my report sheet. I was hoping it would do that anyway, but even after I filter my data the Report sheet still calculates all Minor and Major reports ever recorded. Is there a way to have my calculations be traced within the filters?
Even if there isn't a way to do it with filters, is there another way I could sort my data out so that it could match up with my formulas I already have?
I have attached a screen shot of my reporting sheet calculations for Major and Minor problem reports and a screenshot of part of the data sheet where I am getting those metrics from- just so you have a visual of what I am working with.
Thank you so much Tom!
assume you have dates in column A and as you show, Major and Minor in column L
=countifs(A:A,">="&DateValue("Dec 1, 2012",A:A,"<="&DateValue("Dec 31, 2012"),L:L,"Major")
note the countifs has an "s" on the end. This is a new function provided in Excel 2007 and later.
It allows the use of multiple conditions.
The subtotal formula will work with filtered data. You can specify count, sum, average and several other functions (so the subtotal could produce the count of visible records or the sum of visible records or the average of visible records. You can see the help for details.
However, this would require that all conditions be set with the filter.
That said, there are array formulas built around the subtotal function which would allow some functions to calculate using and additional condition against only the visible rows in the filtered data. This is a very complex formula and I would go with something simple like COUNTIFS and SUMIFS if those will work for you.
---------- FOLLOW-UP ----------
QUESTION: Thank you Tom! But however, it keeps saying I cannot enter in that formula you gave me because I've entered too many arguments for the function.
=COUNTIFS(A:A,">="&DATEVALUE("Dec 1, 2012"),A:A,"<="&DATEVALUE("Dec 31, 2012"),L:L,"Major")
was copied out of excel - so that should be syntactically correct. If you compare it to my sample formula, I had omitted a left paren to the first DateValue.