Forum Discussion
Excel formula error
Please help! So this is my formula "=SUM(COUNTIFS(Feb!H2:H28,{"CHSP"},Feb!A2:A28,{">=03/02/2020"})-COUNTIF(Feb!A2:A28,">07/02/2020"))". Essentially I am trying to calculate whenever we enter CHSP within each week. The problem I am having is that sometimes if there has been no CHSP additions in that week, when it moves to the next week (where the formula has been changed to 10/2 - 14/2, the cell for the week of the 3/2 - 7/2 is coming up with a -1. I cannot figure out why this is happening and I cannot figure out how to fix it. I also have about 20 other spreadsheets that are running this same calculation for their respective spreadsheets and they are not doing this, but they are exactly the same except maybe the CHSP column is G and maybe there are 30 rows etc. An
5 Replies
the best ways to compare date values are
1. get the dates from a cell which is already given
2. use DATEVALUE to create a date number from given text.
Otherwise your comparison in the formula will return an error as you are comparing an actual date with a string.
AllyDee Allow me to explain why your formula doesn't work. It counts (within the given ranges) the number of times where there is CHSP and a date greater than or equal to 3-Feb. And then it deducts the number of times a date occurs after 7-Feb (i.e. from 8-Feb onwards). In cases where your formula comes up with the correct number, it is a coincidence, but never the result of a proper calculation. You want to count CHSP within the date span from 3-Feb up to and including 7-Feb.Thus, the formula below (as already indicated by Twifoo ) gives the answer you want.
=COUNTIFS(FEB!H2:H28,"CHSP",FEB!A2:A28,">=03/02/2020",FEB!A2:A28,"<=07/02/2020")Riny_van_Eekelen Thank you for your explanation, it makes sense and I can see where I went wrong. Really appreciate the help.
