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 columns: ID, Spend, variable . Looks like this:IDSpendvariable 1$4,500x2$0 3$9,000x4$5,000x5$16,000x6$0 7$6,000x8$0x9$21,632 10$0x11$0 12$14,300 13$6,000x14$15,000x15$3,000x16$19,805 17$9,000x18$16,000 19$3,750x20$0x21$7,500x22$12,678 23$27,000 I trying to find the Average of Spend IF Variable="x" AND not to include any Spends in the Average calculation if $0. So, only give me the Average if it's a X in the Variable column but don't drag down the Average calculation with any $zero spends. Thanks!

PFGraham Like this: =AVERAGEIFS(Spend_range, Spend_range, "<>0", variable_range, "x")

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 columns: ID, Spend, variable . Looks like this:

I trying to find the Average of Spend IF Variable="x" AND not to include any Spends in the Average calculation if $0.

So, only give me the Average if it's a X in the Variable column but don't drag down the Average calculation with any $zero spends.

Thanks!