SUMIF does not accept [sum range] as a range, returns 'there is a problem with this formula'

[ShMos]

Hi all,

I am trying to write the following function:

=SUMIF(B2:B11261,AND(>=6,<=10),H2:H11261)

The problem is that, for some reason, excel doesn't accept that the H2:H... range of cells are a range.

I tried manipulating the H2:H... cells, and when just summing they come out fine and are accepted as a range of cells, but for some reason not for the SUMIF function. it returns the error:

even though I place the = before the formula.

I am pulling hairs, why does this happen? How can I fix it?

Notes:

- I'm running on windows 10pro

- I looked at the thread of 'there is a problem with this formula' https://answers.microsoft.com/en-us/msoffice/forum/all/error-display-window-theres-a-problem-with-th...

but that didn't help either.

[#2 · Accepted solution]

@ShMos 

That's like

=SUMIFS(H2:H11261, B2:B11261, ">=6", B2:B11261, "<=10")

[#3]

@ShMos 

=SUMPRODUCT((B2:B11261>=6)*(B2:B11261<=10)*H2:H11261)

An alternative could be SUMPRODUCT. 

[#4]

Thank you! Both solutions worked for me :)

[#5]

@ShMos , glad to help