You are here:

Advertisement

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!

Kathryn

ANSWER:

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.

--

Regards,

Tom Ogilvy

---------- 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.

Thanks!

Kathryn,

=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.

--

Regards,

Tom Ogilvy

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.