Forum Discussion
Help with Excel formula - Countif - Sumproduct
Trying to identify how many items have been completed less than 1wk, less than 2wk, On time, greater than 3wk, greater than 2wk and greater than 1 week from its due date (Column P). Trying to use Column T as a reference of when the item was actually closed.
trying to us the countif formula but I do not know how to start it. Any help would be greatly appreciated.
3 Replies
soccer85 I have added some formulae to your workbook and made one fundamental change. Column U now calculates (Close date -/- Due date) / 7 to determine the number of weeks. By using custom formatting, numbers are displayed as "Greater" (positive), "Less" (negative) or "Equal" (zero). But the underlying values are still numbers that are used in the table at the botton, where COUNTIF is used to summarise the results as desired.
Thanks it kinda make sense. However, after looking this over. I am trying to eliminate column u i just want to nest the formula i created in column v (If statement) into a countif/sumproduct or whatever works so it looks cleaner. The formula should start on t51. Not sure if the formula in column U can be simplified?
at the end of the day i just want to create a column graph showing the number of items that are with these specific tags" Quick Turnaround" "On Time" etc....
Thanks again.
soccer85 Oh my, what a formula! Why not create a lookup table with the options for each status. An example is included in Sheet4. Then you can avoid these horrible nested IF formulae with hard-coded boundaries and descriptions.
Perhaps an approach you want to apply in other areas of your schedule as well. It seems you have made it overly complicated, but that's just my own humble opinion.
