Sumifs formula, indirect tab, and coloumn header

[gman17295]

I have formula here

=SUM(SUMIFS(INDIRECT("'"&$A$2&"'!X:X"),INDIRECT("'"&$A$2&"'!B:B"),"FS PAPER",INDIRECT("'"&$A$2&"'!N:N"),{"GA PACIFIC COMMERCIAL BUS","GEORGIA PACIFIC CONSUMER PRODUCTS,"},........................))


but the last part of the formula I need it to calculate look on the indirect tab if BE1 = "Deviated" (this is the column name) then sum where BE:BE = Y. Otherwise it won't sum.

Any help would be much appreciated!

Thanks!

[FDibbins]

hard to offer a suggestion without seeing what you are working with? suggest uploading a sample workbook

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[gman17295]

Attahced is a visual look at what I am trying to accomplish. If you go to the costworksheet page I want to Sumifs Column D if column B= FS PAPER, and Column C = GA PACIFIC COMMERCIAL BUS, and where cell E1= Deviated sum the Y's but if cell E1 does not = dont look in column E. Also check if cell G1= Deviated then sum the Y's but if cell G1 does not equal Deviated then dont look in column G.

So I want the total in cell E6 to equal 200.

I don not want to use sumproducts either becasue it is too slow. Any suggestions?

Thank you!

[FDibbins]

ok, not sure if this is what you want, i cleaned up your formula a bit and then basically doubles up on it for the 2nd Y...

=SUMIFS(INDIRECT("'"&$A$2&"'!D:D"),INDIRECT("'"&$A$2&"'!B:B"),"FS PAPER",INDIRECT("'"&$A$2&"'!C:C"),"GA PACIFIC COMMERCIAL BUS",INDIRECT("'"&$A$2&"'!E:E"),"Y")+SUMIFS(INDIRECT("'"&$A$2&"'!D:D"),INDIRECT("'"&$A$2&"'!B:B"),"FS PAPER",INDIRECT("'"&$A$2&"'!C:C"),"GA PACIFIC COMMERCIAL BUS",INDIRECT("'"&$A$2&"'!g:g"),"Y")

let me know if this works for you?

[gman17295]

Great! Thanks for your help!

[FDibbins]

ok, not sure if you are still following this, but here is a better version for you...

=SUMPRODUCT(INDIRECT("'"&$A$2&"'!D3:D100"),(INDIRECT("'"&$A$2&"'!B3:B100")="FS PAPER")*(INDIRECT("'"&$A$2&"'!C3:C100")="GA PACIFIC COMMERCIAL BUS")*(SIGN((E3:E100="Y")+(G3:G100="Y"))))

[gman17295]

Ok well I choose not to use SUMPRODUCT becasue it is alot slower.

[FDibbins]

sumproduct is only slow if you use large ranges, or as in your case, entire columns or rows. thats why i restrcted it to 100 rows. buit the correct way of doing something in excel is...whichever way works best for you