• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nesting date calculations within Countifs

W

Wulluby

Member
I'm not sure what I'm doing wrong here but any pointers would be appreciated.


I am using the following formula

=COUNTIFS('Gantt Chart'!H8:H1000,">0",'Gantt Chart'!I8:I1000,"",'Gantt Chart'!F8:F1000,"<MIN(INT((TODAY()-'Gantt Chart'!AN5)/7)+1)")


Value of the date calc is 5 and I expect the result to be 12 but I keep getting 0


I've tried

=COUNTIFS('Gantt Chart'!H8:H1000,">0",'Gantt Chart'!I8:I1000,"",'Gantt Chart'!F8:F1000,"<VALUE('Gantt Chart'!AX5)")


AX5 being a cell doing the date calculation with a result of 5 and I still get 0.


I tried substituting just for the simple 5,

=COUNTIFS('Gantt Chart'!H8:H1000,">0",'Gantt Chart'!I8:I1000,"",'Gantt Chart'!F8:F1000,"<5")


And hey presto I get my expected 12.


In another cell I've tried =2+VALUE('Gantt Chart'!AX5) and got the expected 7 and also tried =2+MIN(INT((TODAY()-'Gantt Chart'!AN5)/7)+1) to get my expected 7.


Is this something to do with the 'Is less than' symbol being used before something other than a figure?


Thanks in advance.
 
Hi ,


You are right ; but it is better to always follow the rule - when specifying the criteria , use only the signs "=" , "<" , ">" and so on , within quotes. Any value such as 5 can be outside the quotes ; thus ">"&5 will also work. This is concatenating the sign with the value ; when the value is just a number such as 5 , then ">"&5 is interpreted the same way as ">5" ; but when the value is a variable , such as :


MIN(INT((TODAY()-'Gantt Chart'!AN5)/7)+1)


then , having this within the quotes will not give the result that you expect. Change this to the proper :


"<"&MIN(INT((TODAY()-'Gantt Chart'!AN5)/7)+1)


and it should work.


However , another point to be looked at is what the formula MIN(INT((TODAY()-'Gantt Chart'!AN5)/7)+1) is supposed to return ; the MIN function requires two values to decide on which is the lower value ; by giving it only one parameter , what actually do you want should happen ? It is the same as INT((TODAY()-'Gantt Chart'!AN5)/7)+1.


Narayan
 
Thanks Narayan,


I see the sense in that. Have just gone to test to see if it works but got a surprise when I opened it up here at work, on my XP machine using 2003. Countifs appear to be a no no. Always a challenge, never a chore...except maybe that one.


I will test tonight back on 2010 though. I was able to check your point about the date and you're right, the MIN was not needed. The function was to look at a cell where I enter the project start date, this then updates which week we are in on the project.


Just to give some more context on what I am doing with the countifs that were giving me problems. I was looking at Chandoo's project tweetboard idea and liked it but just thought it needed to dynamically update itself, so for example:


It's week <?> of the project and so far it's <?%> done. Currently we have <?> ongoing activities and <?> finished activities.


Would become:

="It's week "&INT((TODAY()-'Gantt Chart'!AN5)/7)+1&" of the project and so far it's "&VALUE('Dashboard'!S2)&"% done. Currently we have "&COUNTIFS('Gantt Chart'!H8:H1000,">0",'Gantt Chart'!I8:I1000,"")&" ongoing activities and "&COUNT('Gantt Chart'!J8:J1000)&" finished."


Albeit the %age needs tweaked to make the calculation itself...and the countifs not working in 2003.
 
Wulluby


In 2003 use:

Code: 
=SUMPRODUCT(('Gantt Chart'!H8:H1000>0)*('Gantt Chart'!I8:I1000=""))

instead of

=COUNTIFS('Gantt Chart'!H8:H1000,">0",'Gantt Chart'!I8:I1000,"")


ps: Countifs(), Sumifs() and Averageifs() were introduced in Excel 2007 and aren't backward compatible.
 
Nice one Hui thanks.


If I want to add to the array to that to make a case of 'If this cell is greater than 0 and this other cell is blank and a 3rd cell is less that todays week in the project' I would imagine the argument to be, (taking into account Narayan's point about the variable):

=SUMPRODUCT(('Gantt Chart'!H8:H1000>0)*('Gantt Chart'!I8:I1000="")*('Gantt Chart'!F8:F1000"<"&INT((TODAY()-'Gantt Chart'!AN5)/7)+1))


Can I stack up SUMPRODUCT this way with a variable? Or have I got my basic grammar wrong with this structure?


This would sit in


="Of the "&SUMPRODUCT(('Gantt Chart'!H8:H1000>0)*('Gantt Chart'!I8:I1000=""))&" ongoing activities we have, "&to be inserted here&" of them are delayed...
 
Would love to, but something in that formula wasn't right :)


I was trying to use the "<"& before the variable which SUMPRODUCT wasn't having.


Got it now with =SUMPRODUCT(('Gantt Chart'!H8:H1000>0)*('Gantt Chart'!I8:I1000="")*('Gantt Chart'!F8:F1000<INT((TODAY()-'Gantt Chart'!AN5)/7)+1))


Thanks all for the input, much appreciated.
 
You must log in or register to reply here.
Back
Top