Summarize data on multiple criteria

Input this into cell D7:
=IF(AND($C$4="NA",$D$4="NA",$E$4="NA"),SUMIF(Data!D$2:D$160,$C7,Data!$L$2:$L$2160),IF($D$4="NA",SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4),IF(E4="NA",SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4),SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4,Data!$Q$2:$Q$160,$E$4))))

Input this into cell E7:
=IF(AND($C$4="NA",$D$4="NA",$E$4="NA"),SUMIF(Data!D$2:D$160,$C7,Data!$E$2:$E$160),IF($D$4="NA",SUMIFS(Data!$E$2:$E$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4),IF(F4="NA",SUMIFS(Data!$E$2:$E$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4),SUMIFS(Data!$E$2:$E$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4,Data!$Q$2:$Q$160,$E$4))))

Thank you for your reply. It will take some time for me to understand the exact working of this formula. Meanwhile, I thought of testing it and here are my observations:
1. This criteria was already working
2. It works without any issues
3. If I select 2016 Dec or 2017 Jan, it fills up only values for name "SN" in row7. The rest of the rows are blank. This should ideally show monthwise count for all names for the month of Jan in 2017
4. If I select all three filters. For instance 2017 Jan and 1 as date, it shows zero values for all names. This criteria does not work at all. This should ideally show daywise count for all names for 1st Jan 2017

Originally Posted by excelisfun009

I think there is a built-in feature (Pivot Table) which suits your need.This will eliminate the chances of duplicates as you have mentioned Name PS 2 times in your report,
See if this helps this?

Yes I understand pivot table does the work but I am looking for a formula based solution (if there is any) as demanded by the end user. I mentioned it in my original post as an EDIT. I initially tried the pivot way but the user does not want to keep shuffling the fields and need a very easy way of dropdowns and since then I am breaking my head on this.
And yes, sorry for the two identical names "PS"

Sorry, my first posting was incomplete and little bit incorrect. Try this:

First there were some small errors in my formulas, but I think I've fixed them here:
In cell D7 input this:
=IF(AND($C$4="NA",$D$4="NA",$E$4="NA"),SUMIF(Data!D$2:D$160,$C7,Data!$L$2:$L$160),IF($D$4="NA",SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4),IF($E$4="NA",SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4),SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4,Data!$Q$2:$Q$160,$E$4))))

In Cell E7 input this:
=IF(AND($C$4="NA",$D$4="NA",$E$4="NA"),SUMIF(Data!D$2:D$160,$C7,Data!$E$2:$E$160),IF($D$4="NA",SUMIFS(Data!$E$2:$E$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4),IF($E$4="NA",SUMIFS(Data!$E$2:$E$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4),SUMIFS(Data!$E$2:$E$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4,Data!$Q$2:$Q$160,$E$4))))

Drag these formulas down to copy them to all of the rows corresponding to the names.

Now, go into your data sheet and in cell Q2 input this formula:
=TEXT(C2,"dd")

Drag that down to copy to all of the rows.

Now, I think your table should populate with the figures you want.

Let me know if that works out for you.

In D7

IF(AND($C$4="NA",$D$4="NA",$E$4="NA"),SUMIF(Data!D$2:D$160,$C7,Data!$L$2:$L$2160),IF($D$4="NA",SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4),IF($E$4="NA",SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4),SUMIFS(Data!$L$2:$L$160,Data!D$2:D$160,$C7,Data!$A$2:$A$160,$C$4,Data!$B$2:$B$160,$D$4,Data!$Q$2:$Q$160,$E$4))))

Highlighted E4 should be absolute

same for formula in E7

and Column Q is a helper

=DAY(C2) copy down

Haha, oh yeah! "Column Q is a helper" all right. :-)

Originally Posted by excelisfun009

John Topley & SamulMRoth both Rocked..........
Ahsome solutions indeed....

@JohnTopley
Thanks for highlighting the absolute and bringing it to my notice. It helps when the formula is too big

@sabha
I really don't know. I don't think I've ever worked with anything that auto sorts based on changing criteria.

NO issues! You have helped me a lot.
Let me know what would you want to suggest on point no.2 of showing the time in time format than in minutes.

I think I would probably try to get the data sheet to provide the times in seconds in column L, then when it is summarized in the report sheet have it formatted to display hh:mm:ss . That's just my initial idea about how to approach it though.

In E of "Report"

=TIME(INT(SUMIF(Data!$D$2:$D$160,C7,Data!$E$2:$E$160)/60),MOD(SUMIF(Data!$D$2:$D$160,C7,Data!$E$2:$E$160),60),0)

format cell as [h]:mm