Function MySumIf(ItemNum As String)
Application.Volatile
Dim i As Single
Dim answer As Single
Dim wks As Worksheet
answer = 0

For Each wks In ThisWorkbook.Worksheets
answer = answer +
Application.WorksheetFunction.SumIf(wks.Range("A3: A40"), _
ItemNum, wks.Range("E3:E40"))

Next wks
MySumIf = answer
End Function

My spreadsheet "explodes" a Finished Good (FG) recipe, which includes raw
materials (RM) and Subassemblies (SA), to the raw material level. There 191
sheets. Most of these are FG or SA recipes, which list an item number or
recipe number in column A and an amount in column E (starting on row 3).
Cell A1 on each recipe page has the Recipe Number, and Cell C1 has
"=mysumif(A1)" Finally there is a Raw Material Page which lists all the raw
material numbers in column B. Column N is the Amount Required, and has the
function "=mysumif(B2)". The user enters a number of FGs to be produced on a
FG page and the spreadsheet populates column N of the RM page with the
correct amounts.

Everything has been working great for several months. However, with a new
item we just added, some of the Raw Materials are not calculating (the result
is 0). The recipe in question has 27 rows, so it's not due to the UDF
checking only 40 lines of each page.

The weird part is that if I manually do an "=sumif(<rng,<item#,<rng2)"
elsewhere on the RM page, it will then calculate, but only for the range
entered in the "=sumif..." If I add a second sumif with another range (when
the RM is found on two or more recipes), then the results of the second sumif
will be included in the result for the "mysumif".

Strangely, if I then delete the "sumif's" the "mysumif" does not go back to 0.

Is there a limit to the number of pages a UDF (or a For Each loop) can
iterate through? Can anyone think of another culprit?

FWIW, I'm running XL2003, Win XP Pro SP3 and have 2 GB of RAM.