An array equation with named ranges using multiple criteria...help!

[sabrinam]

I'm using the following array equation with these two named ranges and it works perfectly:

=SUM(IF(Sabrina_NGLTypeWell_Test1=D38),Sabrina_NGL_Test1))

The problem is, I need there to be more criteria within my equation and so I've made more named ranges, except as soon as I add another one using the following equation, it stops working and gives me the #VALUE error.

=SUM(IF(AND(Sabrina_NGLTypeWell_Test1=D38,Sabrina_NGLProduct_Test1=E38),Sabrina_NGL_Test1))

Any idea what I'm doing wrong? Or alternative solutions?

Thanks

[XOR LX]

Hi,

With array formulas, using the AND function does not give the expected results as it would with non-array formulas. You need to use multiplication to create AND logic within an array. Try:

=SUM(IF((Sabrina_NGLTypeWell_Test1=D38)*(Sabrina_NGLProduct_Test1=E38),Sabrina_NGL_Test1))

Regards

[sabrinam]

Thanks! This worked! Really appreciate it.

[Jonmo1]

Try

=SUMIFS(Sabrina_NGL_Test1,Sabrina_NGLTypeWell_Test1,D38,Sabrina_NGLProduct_Test1,E38)