Hi All,

need a bit of help. I have a data set below in table 1 which i want to populate table 2 based on if and condition of AND but for some reason I am not able to get the correct countif data. Can anyone please verify where I am going wrong? Thanks a lot in advance.


table 1
Sl No. Quarter Month JIRA Nos Story Point Hrs/JIRA
1 Q1 2017 Jan-17 abcd 1 2
2 Q4 2017 Feb-17 bahb 2 2
3 Q4 2017 Feb-17 bahb 3 2
4 Q2 2017 Feb-17 bahb 2 2
5 Q2 2018 Feb-17 bahb 5 2
6 Q3 2017 Feb-17 bahb 2 2
7 Q1 2017 Feb-17 bahb 2 2
8 Q4 2017 Feb-17 bahb 4 2
9 Q1 2017 Feb-17 bahb 3 2
10 Q1 2017 Feb-17 bahb 2 2
11 Q1 2017 Feb-17 bahb 2 2
12 Q1 2017 Feb-17 bahb 5 2

table 2
Story Point Q1 2017 Q2 2017 Q3 2017 Q4 2017
1
2
3
4
5
6
7
8
9
10
11
12


the formula i'm trying to use is as below to populate the countif data in table 2 is as example -

=IF((AND($B$3:$B$998=$I$2,$E$3:$E$998=$H3)), COUNTIFS($E$3:$E$998,$H3),"")

I am not getting the correct count for all story points in table2, example its showing me data for 1,2,4 only with the above formula rest is not counting. Can anyone please help me out.

thanks a lot in advance