SUMIF INDIRECT formula does not wok on similat worksheets

Try this:

=SUMIF(INDIRECT("'"&$B5&"'!C:C"),E$4 & "*",INDIRECT("'"&$B5&"'!R:R"))

Failing that, as screenshots are of little practical value, prepare and attach a small sample workbook to demonstrate the problem.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT a release number like Excel version 14.0.6123.5001) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

As you have a space in the sheet name 'Jan KDS', you will need to include those apostrophes, so try it this way:

=SUMIF(INDIRECT("'"& $B5 & "'!C:C"),E$4 & "*",INDIRECT("'"& $B5 & "'!R:R"))

Ensure that you don't have any leading or trailing spaces in the tab name and in column B.

Hope this helps.

Pete

EDIT: Sorry Ali, I thought yours was just an Admin note on first reading - I now see that you have provided the same solution.

Pete

Yes, I did, but I don't think the OP has seen it, either!!! At least your saying the same thing will reinforce my suggestion.

FWIW... running SUMIF with INDIRECT is at least 10 x slower than using SUMIF without INDIRECT. So, don't use whole column ranges... use sensible, future-proof ranges, or dynamic named ranges to speed up the initital calculation, and to minimise recalculation times (INDIRECT recalculates every time anything changes).

You are most welcome and thanks for the rep.