I am wondering if there is a way to sum with criteria across sheets without using INDIRECT.

Here is my data and formula:

Excel Workbook | ||||
---|---|---|---|---|

A | B | |||

1 | ID | Units | ||

2 | ID102 | 3 | ||

3 | ID100 | 3 | ||

4 | ID102 | 1 | ||

5 | ID102 | 4 | ||

6 | ID101 | 5 | ||

7 | ID102 | 10 | ||

8 | ID100 | 5 | ||

9 | ID100 | 8 | ||

10 | ID101 | 3 | ||

11 | ID101 | 1 | ||

Jan |

Excel Workbook | ||||
---|---|---|---|---|

A | B | |||

1 | ID | Units | ||

2 | ID101 | 8 | ||

3 | ID100 | 7 | ||

4 | ID102 | 4 | ||

5 | ID101 | 8 | ||

6 | ID102 | 6 | ||

7 | ID101 | 10 | ||

8 | ID101 | 4 | ||

9 | ID102 | 10 | ||

10 | ID102 | 10 | ||

11 | ID102 | 2 | ||

Feb |

Excel Workbook | ||||
---|---|---|---|---|

A | B | |||

1 | ID | Units | ||

2 | ID101 | 3 | ||

3 | ID102 | 7 | ||

4 | ID102 | 5 | ||

5 | ID102 | 6 | ||

6 | ID100 | 7 | ||

7 | ID100 | 6 | ||

8 | ID100 | 1 | ||

9 | ID100 | 1 | ||

10 | ID101 | 2 | ||

11 | ID102 | 5 | ||

Mar |

Excel Workbook | |||||
---|---|---|---|---|---|

A | B | C | |||

1 | Sheet Names | Crtieria | |||

2 | Jan | ID101 | |||

3 | Feb | TOTAL | |||

4 | Mar | 44 | |||

Sum |

Cell Formulas | ||
---|---|---|

Range | Formula | |

C4 | =SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!A2:A11"),C2,INDIRECT("'"&A2:A4&"'!B2:B11"))) |