I sought help regarding this once, but I failed to outline my problem.
This time I am happy to share the sheet with dummy data in hope it explains my problem a bit better: Link to the sheet
My issue is the following:
In column E I am counting the number of opportunities for a rep (listed in column A). The data I am considering is in a separate sheet named "Pipeline". I do this with the countif formula and I use additional criteria to filter on date as well. My dates for february are in B4 and G4, because I only want to see opportunities in February.
My formula looks like this:
=countIFS(Pipeline!$A:$A,$A7,Pipeline!$F:$F,">="&$B$4,Pipeline!$F:$F,"<="&$G$4)
This works perfectly fine. However, sometimes I have two opportunities in my pipeline sheet with the same name (these are split opportunities). If an opportunity has the same name it should be counted only once. I can't seem to find an easy way to update my countif formula.
In the dummy sheet I shared above, you can see that Peter has two "New - CC Tech" opportunities. I want this to count as one opportunity. Everything I googled so far suggests using rather complex formulas, which is not so easy as I have multiple criteria in the formula that I need to filter my results (such as name of the rep and dates). Please feel free to suggest a solution within the sheet above and play around with it.
I really appreciate the help!
Try this ('unique' based on A,B and F)
=query(unique({Pipeline!A:B,Pipeline!F:F}),"select count(Col1) where Col1='"&A7&"' and Col3>=DATE'"&TEXT($B$4,"yyyy-MM-dd")&"' and Col3<=DATE'"&TEXT($G$4,"yyyy-MM-dd")&"' label count(Col1) '' ")
or, if you consider that the date could be different between two lines ('unique' based only on A and B, the date could be different but within the limits)
=query(unique({Pipeline!A$2:B,arrayformula((Pipeline!F$2:F>=$B$4)*(Pipeline!F$2:F<=$G$4))}),"select count(Col1) where Col1='"&A7&"' and Col3>0 label count(Col1) '' ")
In this second formula, we construct a matrix with A, B and 0/1 (which is the result of the question: is F within limits), then we apply unique and we query when Col3 is equal to 1 and Col1 the name we are looking for