Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF formula that crosses multiple sheet tabs
I have 14 sheet tabs that are named 1, 2, 3, 4, .... 14. These tabs are
positioned next to each other in the workbook. I am trying to create a summary page that does a "SUMIF" across all of these tabs based upon criteria that exists in cell B3 of the Summary tab. Here are the specifics: Sheets 1-14 have product descriptions in column G Sheets 1-14 have sales for each product in column K cell B3 on Summary tab = "Soup" Here is the formula I currently have in cell B4 of the Summary tab: =SUMIF('1:14'!$G$9:$G$10000,B3,'1:14'!$K$9:$K$1000 0) The formula results in a #VALUE error. I tried entering as an array formula too (Ctrl+Shift+Enter), but got the same result. Clearly the SUMIF formula is not working, but is there another alternative to accomplish the same goal? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF formula that crosses multiple sheet tabs
=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("1:14"))&" !$G$9:$G$10000"),B3,INDIRECT(ROW(INDIRECT("1:14")) &"!$K$9:$K$10000")))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JDaywalt" wrote in message ... I have 14 sheet tabs that are named 1, 2, 3, 4, .... 14. These tabs are positioned next to each other in the workbook. I am trying to create a summary page that does a "SUMIF" across all of these tabs based upon criteria that exists in cell B3 of the Summary tab. Here are the specifics: Sheets 1-14 have product descriptions in column G Sheets 1-14 have sales for each product in column K cell B3 on Summary tab = "Soup" Here is the formula I currently have in cell B4 of the Summary tab: =SUMIF('1:14'!$G$9:$G$10000,B3,'1:14'!$K$9:$K$1000 0) The formula results in a #VALUE error. I tried entering as an array formula too (Ctrl+Shift+Enter), but got the same result. Clearly the SUMIF formula is not working, but is there another alternative to accomplish the same goal? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create multiple sheet tabs from multiple cells. | Excel Worksheet Functions | |||
Sumif for multiple tabs | Excel Worksheet Functions | |||
view excel sheet tabs in multiple rows | Setting up and Configuration of Excel | |||
selecting multiple sheet tabs and open another workbook | Excel Discussion (Misc queries) | |||
printing multiple sheet tabs to image file | Excel Discussion (Misc queries) |