Averageifs using cell reference in criteria

Hi there,

I'm trying to use AVERAGEIFS to take the average of the values in a column that are > a low cutoff value and < a high cutoff value.

In my sheet, the cutoff values are calculated in two cells, and I need to refer to those values in my AVERAGEIFS formula.

Here's what I've tried so far:

=AVERAGEIFS(H2:H81,H2:H81,">B10",H2:H81,"<B11")

This gives me a #DIV/0! error.

If I replace the cell references in the criteria clauses (">B10" and "<B11") I get the correct answer, so I guess it's not handling these sections correctly.

Can anyone tell me what I'm missing?

Thank you!

Zoe

Try
=AVERAGEIFS(H2:H81,H2:H81,">"&B10,H2:H81,"<"&B11)

Yes! Excellent - thank you so much for the quick response, that worked perfectly.

The syntax of this baffles me... What does the & indicate?

You're welcoem.

The & is a shortcut for concatenate, to join 2 text strings together into 1.

So a normal sumifs syntax for greater than some number is ">10"
When you try to put a cell reference for the 10 like ">B10"
It reads B10 literally as a text string "B10" (not the contents of the cell B10)

so ">"&B10
Takes the contents of the cell B10 as a text string, and joins it with the text string ">"
So if B10 = 5 or whatever, then it's ">5"

That's really useful info, thanks for your help!

I have a problem similar to the one in this thread, I'd like to point to the text contents of a cell to use as criteria in my Sumifs function. My formula currently looks like this:

=SUMIFS(Data!B:B,Data!A:A,"*CIP-168*")

I want to pick up all cells in my criteria range with the text "CIP-168" in them, but I don't want to have to type it in my Sumifs formula, I want to reference a cell with the text "CIP-168" in it. I appreciate any help, thank you!

Thank you.