google-sheetsformulareturn-valuerecordtype

Return one value from a report list, not sum or record count in Sheets


I am trying to use a formula to return one value from a list of record counts.

Here is the example so you all know what I mean.

What I am trying to do is return in one field the amount of sold software per rep, which I do with the formula SUMIFS. I also know how to return the amount of record types for a certain value using COUNTIFS. However, the quota is different every month and in my report list the month could appear several times. So for example I have 4 record types of May, which all reflect the quota of that rep for that month. In a field (see exercise linked) I want to return that quota but only one time. I can't do it with COUNTIFS or SUMIFS. I could count SUMIFS of all and then divide it by the COUNTIFS...but there must be a simpler way?

Thankful for any help I can get!


Solution

  • EDIT:

    You can use the formula below for the employee "Susan" and drag down.

    =IFERROR(INDEX(FILTER(D$2:F,D$2:D=K$3,C$2:C=J5),1,3),"")