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 wit...
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.