How about:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&data&"'!a31"),$H$ 11),
N(INDIRECT("'"&data&"'!j29")),N(INDIRECT("'"&data& "'!j36")))

turrucan wrote:

In this case formula multiplies j29 and j37 but since data is defined as an
array (like rev_1,rev_2,rev_3) , product function does not recognize it as an
array and take product of rev_1!j29 and rev_1!j37.

I tried applying Ctrl , Shift and Enter, and result did not change .

The first formula I sent was capable of arrays because of the sumproduct
formula

"Bob Phillips" wrote:

Maybe a small tweak to Lori's suggestion

=PRODUCT(SUMIF(INDIRECT("'"&data&"'!a31"),$H$11,
OFFSET(INDIRECT("'"&data&"'!j29"),{0,7},0)))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"turrucan" wrote in message
...
thanks but your formulation simply adds j29's and j35's instead of
multiplying them

"Lori" wrote:

=SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!a31"),$H$11 ,
OFFSET(INDIRECT("'"&data&"'!j29"),{0,7},0)))

turrucan wrote:

I have a indirect formulation that reads from a dynamic range called
data
like these

SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!a31"),$H$11, INDIRECT("'"&data&"'!j29")),SUMIF(INDIRECT("'"&dat a&"'!a31"),$H$11,INDIRECT("'"&data&"'!j36")))

But as you noticed I have to write sumif condition twice to take
sumproduct
of two cells in a sheet ( j29 and j36 ).
Is there an easier way to write this equation with only one sumif
formula ?