Sumproduct using multiple sheets

  • Thread starter Radhakant Panigrahi
  • Start date
R

Radhakant Panigrahi

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
"insert>name>define" 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct of multiple sheets 0
sumproduct of multiple sheets 5
Pivot from multiple sheets 1
Pivot from multiple sheets 0
Sumproduct formula needed 2
sumproduct() function resulted in an error #DIV/0 2
couontif function 4
Sumproduct across mutliple sheets - deleting row in one sheet 2

Top