Forum Discussion

PFGraham's avatar
PFGraham
Copper Contributor
Jun 01, 2023
Solved

CountIF or SumIf or SubtotalIf - array question

Hello Excel Geniuses,   I'm working on an insane spreadsheet that I 'got hold' of, trying to do some extractions on desperate sets within it.   Anyway, the problem today is related to this set: 3...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 01, 2023

    PFGraham 

     

    Like this:

     

    =AVERAGEIFS(Spend_range, Spend_range, "<>0", variable_range, "x")

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 02, 2023

PFGraham 

or

=AVERAGE( FILTER(SpendRange, SpendRange*(variableRange="x")) )
  • PFGraham's avatar
    PFGraham
    Copper Contributor
    Jun 02, 2023
    Another way - I'll try that next, mostly for fun and to understand FILTER (never heard of that before).

    Very cool, thanks!

Resources