0

I have this issue I haven't been able to work out.
I have several teams with a lot of members. Each member uploads a lead to a system that has another column that writes down when the lead took to a proposal. I need to count how many leads did the team upload that actually took to a proposal.

It looks something like this (simplified):

Data

I want to count how many leads uploaded from mauricio's team actually have a date under proposal date. I tried doing something like this but the if stayed at the first cell (in this case "mauricio@trial").

trial formula

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Mauricio Barrera
  • 1

2 Answers2

0
=SUMPRODUCT(($C$8:$C$19="")*($D$8:$D$19=A1))+SUMPRODUCT(($C$8:$C$19="")*($D$8:$D$19=B1))+SUMPRODUCT(($C$8:$C$19="")*($D$8:$D$19=B2))+SUMPRODUCT(($C$8:$C$19="")*($D$8:$D$19=B3))

Basically I did it with a two criteria count for each one of the team members then added them together. The formula for one team member for easier reading is:

=SUMPRODUCT(($C$8:$C$19="")*($D$8:$D$19=A1))

the part in between the brackets making a logical comparison is your criteria for your matching. The * acts the same as an AND statement. Since you need to know for this member OR this member you need a SUMPRODUCT (or other formula) for each member add them together to get your over all results

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • That works, the problem is that I have around 95 teams so that's not efficient in the long run hehe. I tried another thing in which I first use a filter and then try to do the array stuff but doesn't work. I first filter the database to get all the members on one team and then try to use the other conditions: =COUNTIFS(Sheet12!N:N,filter(Auxiliar!B:B,Auxiliar!C:C=A8,Auxiliar!F:F>0),Sheet12!F:F,"") – Mauricio Barrera Apr 05 '16 at 15:11
0

You can use an array, but the result will be an array; and also multidimensional vectors are difficult to handle.

So I split the two columns for the team leader and members and added them together:

=SUM(COUNTIFS(E48:E59,B41:B43,D48:D59,"<>"))+COUNTIFS(E48:E59,A41,D48:D59,"<>")

Since the first COUNTIFS returns an array, the items must be added; and also the formula should be entered as an array formula, by holding down ctrl+shift while hitting enter. If you do that correctly, Excel will place braces {...} around the formula in the formula bar.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • That works, the problem is that I have around 95 teams so that's not efficient in the long run hehe. I tried another thing in which I first use a filter and then try to do the array stuff but doesn't work. I first filter the database to get all the members on one team and then try to use the other conditions: =COUNTIFS(Sheet12!N:N,filter(Auxiliar!B:B,Auxiliar!C:C=A8,Auxiliar!F:F>0),Sheet12!F:F,"") – Mauricio Barrera Apr 05 '16 at 15:11
  • @MauricioBarrera So your stated problem does not really reflect the true scope of what you are trying to solve. That makes things more difficult. Why not just add a fourth column to your lower table listing the team for each line; then you could easily use a filter, or pivot table, or maybe even use the `AGGREGATE` function. – Ron Rosenfeld Apr 05 '16 at 17:07