sumif over multiple sheets
 

am trying to get a sum for a particular item in a workbook. There are 43
sheets in the workbook. I was doing sumif and it says the formula is too long
and I was on sheet 40. Any ideas on how to get it to pull the info I need
from multiple sheets?

sumif over multiple sheets
 

Hi,

I don't believe sumif can be used on 3d ranges so another way would be to
place a sumif on each sheet then

=SUM(Sheet1:Sheet43!A1)

Which will sum a1 on sheets 1 to 43

Mike

"Fritzi" wrote:

am trying to get a sum for a particular item in a workbook. There are 43
sheets in the workbook. I was doing sumif and it says the formula is too long
and I was on sheet 40. Any ideas on how to get it to pull the info I need
from multiple sheets?

sumif over multiple sheets
 

One way is you create a list with all the sheet names

Assume list with sheet name is H1:H43

=SUMPRODUCT(SUMIF(INDIRECT("'"&H1:H43&"'!A:A"),"X" ,INDIRECT("'"&H1:H43&"'!B:B")))

will sum B:B where A:A = X

--


Regards,


Peo Sjoblom

"Mike H" wrote in message
...
Hi,

I don't believe sumif can be used on 3d ranges so another way would be to
place a sumif on each sheet then

=SUM(Sheet1:Sheet43!A1)

Which will sum a1 on sheets 1 to 43

Mike

"Fritzi" wrote:

am trying to get a sum for a particular item in a workbook. There are 43
sheets in the workbook. I was doing sumif and it says the formula is
too long
and I was on sheet 40. Any ideas on how to get it to pull the info I
need
from multiple sheets?

sumif over multiple sheets
 

If you have Longre's morefunc.xll installed (see
http://xcell05.free.fr/morefunc/english/index.htm )

There is a lot of useful functions, including adding 3D functions, that you
may find useful.


--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Fritzi" wrote:

am trying to get a sum for a particular item in a workbook. There are 43
sheets in the workbook. I was doing sumif and it says the formula is too long
and I was on sheet 40. Any ideas on how to get it to pull the info I need
from multiple sheets?