Prev: pick up particular number from a table
Next: Fix an image to a cell in Excel
From: Radhakant Panigrahi on 27 May 2010 17:35
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

 | 
Pages: 1
Prev: pick up particular number from a table
Next: Fix an image to a cell in Excel