Sumproduct using multiple sheets
 

Try this€¦

=SUMPRODUCT((Sheet1!A1:A9="Mark")*(Sheet1!B1:B9="A utomatic"))+SUMPRODUCT((Sheet2!A1:A9="Mark")*(Shee t2!B1:B9="Automatic"))+SUMPRODUCT((Sheet3!A1:A9="M ark")*(Sheet3!B1:B9="Automatic"))

In the above formula change the worksheet names Sheet1,Sheet2 and sheet3 to
your sheet names.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Radhakant Panigrahi" wrote:

Hi,

I am using the sumproduct formula for multiple sheets.
Below are my data which is contains from sheet1 to sheet3
when i use the sumproduct formula for in one sheet
=SUMPRODUCT((A1:A9="Mark")*(B1:B9="Automatic")) it gives me the result =3
and if i use the formula in 3 different sheets it will give me the result
3+3+3= 9

To use the sumproduct formula i have defined the sheets in menu
"insertnamedefine" as "ms"
={"Sheet1","Sheet2","Sheet3"}.
Then i have used below formula in sheet 4.
=SUMPRODUCT(COUNTIF(INDIRECT(ms&"!$a1:a9),"MARK"), COUNTIF(INDIRECT(ms&"!$b1:b9),"Automatic"))
In this case i am getting the result as 75 where as it should be 9.

can anybody help me out this whether any modification is required in the
above formula

Name Process
Mark Automatic
Henry Manual
Henry Automatic
Mark Automatic
Mark Manual
Henry Automatic
Mark Manual
Mark Automatic

rgds,
radha

Sumproduct using multiple sheets
 

Hi,

yes i can use the below formula as well but i have 30-40 sheets and it takes
a longtime to execute...so just wanna the sumproduct with defined appraoch.



"Ms-Exl-Learner" wrote:

Try this€¦

=SUMPRODUCT((Sheet1!A1:A9="Mark")*(Sheet1!B1:B9="A utomatic"))+SUMPRODUCT((Sheet2!A1:A9="Mark")*(Shee t2!B1:B9="Automatic"))+SUMPRODUCT((Sheet3!A1:A9="M ark")*(Sheet3!B1:B9="Automatic"))

In the above formula change the worksheet names Sheet1,Sheet2 and sheet3 to
your sheet names.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Radhakant Panigrahi" wrote:

Hi,

I am using the sumproduct formula for multiple sheets.
Below are my data which is contains from sheet1 to sheet3
when i use the sumproduct formula for in one sheet
=SUMPRODUCT((A1:A9="Mark")*(B1:B9="Automatic")) it gives me the result =3
and if i use the formula in 3 different sheets it will give me the result
3+3+3= 9

To use the sumproduct formula i have defined the sheets in menu
"insertnamedefine" as "ms"
={"Sheet1","Sheet2","Sheet3"}.
Then i have used below formula in sheet 4.
=SUMPRODUCT(COUNTIF(INDIRECT(ms&"!$a1:a9),"MARK"), COUNTIF(INDIRECT(ms&"!$b1:b9),"Automatic"))
In this case i am getting the result as 75 where as it should be 9.

can anybody help me out this whether any modification is required in the
above formula

Name Process
Mark Automatic
Henry Manual
Henry Automatic
Mark Automatic
Mark Manual
Henry Automatic
Mark Manual
Mark Automatic

rgds,
radha