sumproduct of multiple sheets

[Radhakant Panigrahi]

Hi,

I am using the below sumproduct formula
=sumproduct((sheet1!A1:A50000="Mark")*(sheet1!A1:A50000="Automatic"))

I have to make the formula for almost 20-25 sheets. so i have to put the
formula in 25 cells to know the result of 25 sheets.

Is there any sumproduct formaula to take the data from all sheet at one go
and give me the result in one cell

regards,
rkp

 

[ozgrid.com]

You are better off Grouping your 25 Worksheets and entering the SUMPRODUCT
Function once with NO sheet references, then use =SUM(Sheet1:sheet25!A1) to
get you grand total. Where A1 on each of the 25 sheets contains your
SUMPRODUCT Function.

 

[Don Guillett]

If you don't want to use the formula for each sheet you can use one formula
that relies on a list of sheets or a defined name with a list of the sheets.
Here is an example where col F has the sheet names
=SUMPRODUCT(SUMIF(INDIRECT(OFFSET(F1,,,COUNTA(F:F))&"!B1"),"PMI",INDIRECT(OFFSET(F1,,,COUNTA(F:F))&"!A3")))

or using a defined name
=SUMPRODUCT(SUMIF(INDIRECT(ms&"!B1"),"PMI",INDIRECT(ms&"!A3")))

 

[Radhakant Panigrahi]

Hi Don,

thanks for your answer, but just to understand where do i need to put my
reference in the function that u have provided...

here in my formaula i have to calculate The Name "Mark" from Column A and
"Automatic" from ColumnB. So where i need to put the "Mark" and "automatic"
as criteria and also where to put the column "A" &A"B" as reference reference.

regads,

=sumproduct((sheet1!A1:A50000="Mark")*(sheet1!B1:B50000="Automatic"))

 

[Don Guillett]

This works for the defined names approach where ms is defined as
={"Sheet1","Sheet2","Sheet3"}

=SUMPRODUCT(COUNTIF(INDIRECT(ms&"!$a1:a21"),"MARK"),COUNTIF(INDIRECT(ms&"!$b1:b21"),"automatic"))

 

[Radhakant Panigrahi]

Hi Don,

I have defined the 3 sheets in Insert>name>define and applied the below
sumproduct formaula, however it is giving me the figure more than what is
actual. Actual figure is 9 where as it is giving me 90

Below is my data. the same data is contained in 3 sheets the Name "Mark"
with "Automatic" is coming 3 times in a sheet and for 3 sheets it is 9,
where as it is coming as 90.

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