Share via

Combining a Let and averageif formula.

Andre Hofer 85 Reputation points
2026-04-15T23:36:06.3633333+00:00

Monthly we collate items bought for our manufacturing franchise to produce KPI stats. This formula works.

=LET(items,$a$1:$a$300,qty,$c$1:$c$300,keys,Parms!$C$2:$C$3,SUM(SUMIFS(qty,items,keys)))

where Parms lists the variables we search for, in this case CNC**

This provides the total number of quantity of all CNC items

User's image

I want to combine the above formula with the AverageIF formula to find the average price of all the items occurrences in order to reduce the number of formulas to use. To note that the prices are not the same for all CNC items.

=IFERROR(AVERAGEIF($a$1:$a$300,"CNC”,$*E$1:$E$300),0)

Is this possible?

Microsoft 365 and Office | Excel | For business | MacOS

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. IlirU 2,491 Reputation points Volunteer Moderator
    2026-04-17T07:29:21.63+00:00

    User's image

    Hi @Andre Hofer,

    (If I understand you correctly)

    I have placed some keys in column G. These keys have an asterisk before and after them.

    Then I applied my formula to cell I2 (see screenshot).

    You can change the ranges in the formula according to your needs.

    =LET(
     item, A1:A300,
      qty, C1:C300,
      avg, E1:E300,
     keys, G1:G10,
     srch, SEARCH(TRANSPOSE(TOCOL(keys, 3)), item),
           SUM(TOCOL(avg / srch, 3)) / SUM(TOCOL(qty / srch, 3))
)

    HTH

    IlirU

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Dora-T 13,745 Reputation points Microsoft External Staff Moderator
    2026-04-16T01:29:22.76+00:00

    Hi Andre Hofer

    Based on your description, I understand that you are using a LET + SUMIFS formula to calculate the total quantity of items that match a set of criteria (for example, “CNC” from your Parms sheet). You would like to use the same criteria to calculate the average price as well, instead of relying on a separate AVERAGEIF with a hardcoded condition.

    However, AVERAGEIF does not support using a range of criteria (such as a list of keys like "CNC*") in the same way as SUMIFS, so it cannot be applied directly in this scenario.

    In this case, you can reuse the same criteria by filtering the matching rows first, and then calculating the average from that filtered result.

    =LET(
    items,$A$1:$A$300,
    price,$E$1:$E$300,
    keys,FILTER(Parms!$C$2:$C$3,Parms!$C$2:$C$3<>""),
    cond,BYROW(items,LAMBDA(x,SUM(--ISNUMBER(SEARCH(keys,x)))>0)),
    IFERROR(AVERAGE(FILTER(price,cond)),0)
)

    You may refer to this approach and adjust the ranges or criteria to fit your environment.

    If I have misunderstood your scenario or if you need further assistance, please feel free to let me know.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?

    1 person found this answer helpful.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.