How to Subtotal Countifs with 3 criteria?

Hello,

I'm new to Excel and this forum and am stumped on how to write a formula that will count status based on the fiscal year and month submitted for a large filtered data set.

Below is sample data with columns A=Status, E=Submitted Date, and F=Fiscal Year. How can I write a formula that will only count the total number of "Pending" statuses in FY 2015 for the month of September?

sample.jpg

I tried =COUNTIFS(A2:A10,"=Pending",E2:E10,MONTH(E2:E10)<>9,F2:F10,2015) on the UNFILTERED list, but it gives me "0" when it should be "2." I cannot figure out if I can add SUBTOTAL for a filtered list.

Thanks for any help offered! Sample.xlsx

Suggest you post a sample workbook with some typical data.

Regards, TMS

Not sure if this solves it, or if this was just a typo, but, remove the = from "=Pending" in the formula

Formula:

Please Login or Register  to view this content.

gives 13


Regards, TMS

Here's another:

Please Login or Register  to view this content.

Thanks for the rep.


Be aware that, although those two formulae may work, they should be used with care. Prior to Excel 2007, you could not use SUMPRODUCT with full column references ... they would generate an error. Although you can use full column reference in 2007 forward, it is not good practice. The reason is that SUMPRODUCT generates a matrix of the cells referenced. In this instance, you will be generating a matrix of 3 x 1 million+ cells.


Regards, TMS