sumproduct of multiple sheets
 

Hi,

I am using the below sumproduct formula
=sumproduct((sheet1!A1:A50000="Mark")*(sheet1!A1:A 50000="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

sumproduct of multiple sheets
 

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.


--
Regards
Dave Hawley
www.ozgrid.com

"Radhakant Panigrahi" wrote in message
...
Hi,

I am using the below sumproduct formula
=sumproduct((sheet1!A1:A50000="Mark")*(sheet1!A1:A 50000="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

sumproduct of multiple sheets
 

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",INDIREC T(ms&"!A3")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Radhakant Panigrahi" wrote in message
...
Hi,

I am using the below sumproduct formula
=sumproduct((sheet1!A1:A50000="Mark")*(sheet1!A1:A 50000="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

sumproduct of multiple sheets
 

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:B 50000="Automatic"))

"Don Guillett" wrote:

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",INDIREC T(ms&"!A3")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Radhakant Panigrahi" wrote in message
...
Hi,

I am using the below sumproduct formula
=sumproduct((sheet1!A1:A50000="Mark")*(sheet1!A1:A 50000="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

.

sumproduct of multiple sheets
 

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"))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Radhakant Panigrahi" wrote in message
...
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:B 50000="Automatic"))

"Don Guillett" wrote:

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",INDIREC T(ms&"!A3")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Radhakant Panigrahi" wrote in message
...
Hi,

I am using the below sumproduct formula
=sumproduct((sheet1!A1:A50000="Mark")*(sheet1!A1:A 50000="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

.

sumproduct of multiple sheets
 

Hi Don,

I have defined the 3 sheets in Insertnamedefine 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


"Don Guillett" wrote:

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"))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Radhakant Panigrahi" wrote in message
...
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:B 50000="Automatic"))

"Don Guillett" wrote:

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",INDIREC T(ms&"!A3")))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Radhakant Panigrahi" wrote in message
...
Hi,

I am using the below sumproduct formula
=sumproduct((sheet1!A1:A50000="Mark")*(sheet1!A1:A 50000="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

.


.