| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF across multiple worksheets
Is it possible to have a SUMIF formula across multiple worksheets? If so,
how? Thank you. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF across multiple worksheets
Yes, but you need to provide more details.
Biff "Fgbdrum" wrote in message ... Is it possible to have a SUMIF formula across multiple worksheets? If so, how? Thank you. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF across multiple worksheets
On my "total" worksheet, I have a set of numbers in Column A.
These numbers appear in column A of 25 other worksheets as well. However, in column B of these 25 other worksheets appear unique corresponding numbers. I want to sum these unique numbers that appear in these 25 worksheets in column B of my "total" worksheet using a sumif formula. "Biff" wrote: Yes, but you need to provide more details. Biff "Fgbdrum" wrote in message ... Is it possible to have a SUMIF formula across multiple worksheets? If so, how? Thank you. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF across multiple worksheets
If your sheet names follow some kind of pattern/sequence like the default
sheet names: Sheet2, Sheet3, Sheet4...Sheet25: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 2:25"))&"'!A1:A10"),A1,INDIRECT("'Sheet"&ROW(INDIR ECT("2:25"))&"'!B1:B10"))) If your sheet names are unique like Alaska, Alabama, Arizona: You have to list the sheet names in a range of cells, say, H1:H25, then: =SUMPRODUCT(SUMIF(INDIRECT("'"&H$1:H$25&"'!A1:A10" ),A1,INDIRECT("'"&H$1:H$25&"'!B1:B10"))) Both formulas do the same thing: Sumif(Sheet_names!A1:A10,A1,Sheet_names!B1:B10) then Sumproduct adds them all up. Biff "Fgbdrum" wrote in message ... On my "total" worksheet, I have a set of numbers in Column A. These numbers appear in column A of 25 other worksheets as well. However, in column B of these 25 other worksheets appear unique corresponding numbers. I want to sum these unique numbers that appear in these 25 worksheets in column B of my "total" worksheet using a sumif formula. "Biff" wrote: Yes, but you need to provide more details. Biff "Fgbdrum" wrote in message ... Is it possible to have a SUMIF formula across multiple worksheets? If so, how? Thank you. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF across multiple worksheets
Biff,
Just wanted to thank you for taking the time. It worked out great. Thanks again. Fabio "Biff" wrote: If your sheet names follow some kind of pattern/sequence like the default sheet names: Sheet2, Sheet3, Sheet4...Sheet25: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 2:25"))&"'!A1:A10"),A1,INDIRECT("'Sheet"&ROW(INDIR ECT("2:25"))&"'!B1:B10"))) If your sheet names are unique like Alaska, Alabama, Arizona: You have to list the sheet names in a range of cells, say, H1:H25, then: =SUMPRODUCT(SUMIF(INDIRECT("'"&H$1:H$25&"'!A1:A10" ),A1,INDIRECT("'"&H$1:H$25&"'!B1:B10"))) Both formulas do the same thing: Sumif(Sheet_names!A1:A10,A1,Sheet_names!B1:B10) then Sumproduct adds them all up. Biff "Fgbdrum" wrote in message ... On my "total" worksheet, I have a set of numbers in Column A. These numbers appear in column A of 25 other worksheets as well. However, in column B of these 25 other worksheets appear unique corresponding numbers. I want to sum these unique numbers that appear in these 25 worksheets in column B of my "total" worksheet using a sumif formula. "Biff" wrote: Yes, but you need to provide more details. Biff "Fgbdrum" wrote in message ... Is it possible to have a SUMIF formula across multiple worksheets? If so, how? Thank you. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIF across multiple worksheets
You're welcome. Thanks for the feedback!
Biff "Fgbdrum" wrote in message ... Biff, Just wanted to thank you for taking the time. It worked out great. Thanks again. Fabio "Biff" wrote: If your sheet names follow some kind of pattern/sequence like the default sheet names: Sheet2, Sheet3, Sheet4...Sheet25: =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 2:25"))&"'!A1:A10"),A1,INDIRECT("'Sheet"&ROW(INDIR ECT("2:25"))&"'!B1:B10"))) If your sheet names are unique like Alaska, Alabama, Arizona: You have to list the sheet names in a range of cells, say, H1:H25, then: =SUMPRODUCT(SUMIF(INDIRECT("'"&H$1:H$25&"'!A1:A10" ),A1,INDIRECT("'"&H$1:H$25&"'!B1:B10"))) Both formulas do the same thing: Sumif(Sheet_names!A1:A10,A1,Sheet_names!B1:B10) then Sumproduct adds them all up. Biff "Fgbdrum" wrote in message ... On my "total" worksheet, I have a set of numbers in Column A. These numbers appear in column A of 25 other worksheets as well. However, in column B of these 25 other worksheets appear unique corresponding numbers. I want to sum these unique numbers that appear in these 25 worksheets in column B of my "total" worksheet using a sumif formula. "Biff" wrote: Yes, but you need to provide more details. Biff "Fgbdrum" wrote in message ... Is it possible to have a SUMIF formula across multiple worksheets? If so, how? Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Functions across multiple worksheets | Excel Worksheet Functions | |||
| Sumif with multiple worksheets | Excel Worksheet Functions | |||
| Update multiple worksheets | Excel Discussion (Misc queries) | |||
| how do I arrange multiple worksheets from the same workbook | Excel Discussion (Misc queries) | |||
| Extracting data from multiple worksheets into a list | Excel Worksheet Functions | |||
Hybrid Mode
