Countif function not calculating correctly
I am trying to use a countifs function to filter data in a MS Excel spreadsheet. Below are my results. As you can see the Math (Outstanding items = previous week's outstanding items - Items Completed + new items isn't working. It doesn't look like it is counting the new items each week. When I manually filter the spreadsheet, the new items are contained in the list of the outstanding items. Below are my formulas:
Items Completed =COUNTIFS('New Items'!$BD$7:$BD$40000,"No",'New Items'!$BA$7:$BA$40000,">="&A2,'New Items'!$BA$7:$BA$40000,"<="&B2)
New Items=COUNTIFS('New Items'!$BD$7:$BD$40000,"No",'New Items'!$AN$7:$AN$40000,">="&A2,'New Items'!$AN$7:$AN$40000,"<="&B2)
Outstanding Items=COUNTIFS('New Items'!$BD$7:$BD$40000,"No",'New Items'!$BA$7:$BA$40000,">"&B2)+COUNTIFS('New Items'!$BD$7:$BD$40000,"No",'New Items'!$BA$7:$BA$40000,"")
It is always off by the number of new items.
Starting Point:03/01/14Items
CompletedNew
ItemsOutstanding Items03/03/1403/07/140240503/10/1403/14/140540503/17/1403/21/1481239703/24/1403/28/1455239203/31/1404/04/149338304/07/1404/11/1463137704/14/1404/18/142765350
Please help. I am new to the countifs function.
|