--One approach is to use your formula in each sheet say in cell C1 and then
in your summary sheet use a formula which will return the totol from all
sheets
=SUM(Sheet1:Sheet3!C1)


--Another approach is to use a hidden column in each sheet say ColC in which
you concatenate the values from A1 and B1
=A1&"|"&B1

and use the below formula in summary sheet which will return the total count
using the named range of worksheets
=SUMPRODUCT(COUNTIF(INDIRECT("'"&ms&"'!C:C"),"Mark |Automatic"))

--
Jacob (MVP - Excel)


"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