Hi. I am quite confused as to why I can't seem to get the filter function to work properly in my if formula. In my attached workbook, you will see that I first use the Unique function to find out how many ranges there are (2), and then use the filter function to find out how many products in those ranges have shortages > 0 (one has 3 shortages, the other 0). I used the formula "=COUNTA(FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0))" to basically make an AND statement where it will only include the rows who range matches the one to the left (i.e. F2, which is Range 1 in this case) and ones with shortages > 0, which is 3 for Range 1. This part works fine, but naturally I get an issue when I try to use COUNTA for Range 2 because no shortage is found as there are 0 shortages (i.e. no rows that meet the filter condition), so it ends up returning a 0, but because the "0" counts as one cell, COUNTA returns 1 when I really want it to return 0.
I then tried using an IF formula to solve this:
"=IF(FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0)=0,0,COUNTA(FILTER(A2:A21,(A2:A21=F2)*(C2:C21>0),0)=0))"
It works fine for range 2 (it gives me back 0), but for some reason it gives me a spill error when I try to use this same formula for Range 1. I don't understand why it decided to try and return the range of cells for Range 1 before it bothers to evaluate the condition. I know I am very close to a solution but I can't figure this problem out. I am very confused, and quite annoyed, so any help is much appreciated.
Bookmarks