View Single Post
 
Old 07-09-2014, 02:22 AM
MoranGail MoranGail is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Jul 2014
Location: Griswold, CT
Posts: 1
MoranGail is on a distinguished road
Default 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
Completed
New
Items
Outstanding 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.
Reply With Quote