Countif not calculating correct value

[abhradipchoudhuri]

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

[humdingaling]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[abhradipchoudhuri]

i have the file ready to upload but when i try to via go advanced the applet is not giving error but neither a drop down where I can select the file. Any other option? I tried in IE, chrome and firefox same issue

[humdingaling]

try other methods mentioned in here
http://www.excelforum.com/showthread.php?t=1128657

[abhradipchoudhuri]

Attachment added

[humdingaling]

Put this in i3 and filldown/right

Please Login or Register  to view this content.

i have differences in r4 and r5 but based on data i believe your manual mock up is incorrect

[avk]

Try in "I3"

Please Login or Register  to view this content.

[abhradipchoudhuri]

Thanks for the help, this seems to work. One question though - what was my mistake if you don't mind sharing.

[avk]

Please note, IF function returns one value if the condition is TRUE, or another value if the condition is FALSE.
AND function returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE.
COUNTIF, one of the statistical functions, to count the number of cells that meet a one criteria & for two or more criteria use Countifs.

If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

[humdingaling]

at the core
if you take the If statement out,

this is the formula you had

Please Login or Register  to view this content.

this only checks one criteria - H3 which is story point only

Please Login or Register  to view this content.

notice the difference with the new formula
it checks against I$2 as well, which is your quarter