Sumproduct function with filter / unfilter

Question

I prepared a table and I wrote below mentioned formula for counting the month basis..

=SUMPRODUCT(--(B$6:B$20<>"");--(MONTH(B$6:B$20)=6))

1- I want the formula work when I use filter.

2- I want to add one more criteria in the formula.

I want to count monthly basis and additionally Status basis.

for example ; for  June-2015 , Issue number 4, Closed number 3 and Cancelled 1.

Do you have any idea?

15.6.2015	Closed
18.6.2015	Closed
25.6.2015	Cancelled
26.6.2015	Closed
11.7.2015	Cancelled
17.7.2015	Closed
17.7.2015	Rejected

Answer 1

Though Pivot table is the best approach but in case because of business / design need, you are looking for formula, below is the formula -

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6,ROW(B6:B20)-ROW(B6),))*(B$6:B$20<>"")*(MONTH(B$6:B$20)=6))

The above formula will give you Total for June month. For applying other criteria, just keep multiplying the criteria. Hence, for example, for Closed ones -

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6,ROW(B6:B20)-ROW(B6),))*(B$6:B$20<>"")*(MONTH(B$6:B$20)=6)*(C$6:C$20="Closed"))

You can build the same for other criteria also.

Answer 2

Hi,

Create a Pivot Table.  Dates have been grouped by months.

Here's a screenshot

Answer 3

Before above mentioned formula, I create Pivot table.

But I couldn't filtered the dates as month. Dates mentioned as "15.08.2015" in my data. When I created the pivot table, rows or coloumns contains the dates as mentioned in data source.

I attached the screen shot