|
Indirect Formulation
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 ? |
Indirect Formulation
=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 ? |
Indirect Formulation
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 ? |
Indirect Formulation
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 ? |
Indirect Formulation
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 ? |
Indirect Formulation
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 ? |
Indirect Formulation
thanks , this is the formula I need .
"Lori" wrote: 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 ? |
| All times are GMT +1. The time now is 10:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com