|
SUMIF help
I am trying to sum 5 sheets into a master sheet by client using the following
formula, I am not sure of how to add all the sheets in the workbook. This is what I got so far for the master sheet =SUMIF(Sheet1!A:A,"approved",Sheet1!C:C) and that works fine for one sheet, how do I apply it so it adds all five sheets at one time for "approved" Please help.! thank you -- Louie |
SUMIF help
One way is to put the same formula in the same place on each sheet and then
=sum(sheet1:sheet21!a2) -- Don Guillett SalesAid Software "Louie" wrote in message ... I am trying to sum 5 sheets into a master sheet by client using the following formula, I am not sure of how to add all the sheets in the workbook. This is what I got so far for the master sheet =SUMIF(Sheet1!A:A,"approved",Sheet1!C:C) and that works fine for one sheet, how do I apply it so it adds all five sheets at one time for "approved" Please help.! thank you -- Louie |
SUMIF help
To start, you'll need a list of your sheet names.
Say in Z1 you enter Sheet1 And drag down to create your Sheet1 to Sheet5 list in Z1 to Z5. Then, try this formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!A:A"),"App roved",INDIRECT("'"&Z1:Z5&"'!C:C"))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Louie" wrote in message ... I am trying to sum 5 sheets into a master sheet by client using the following formula, I am not sure of how to add all the sheets in the workbook. This is what I got so far for the master sheet =SUMIF(Sheet1!A:A,"approved",Sheet1!C:C) and that works fine for one sheet, how do I apply it so it adds all five sheets at one time for "approved" Please help.! thank you -- Louie |
SUMIF help
=sum(sheet1:sheet21!a2)
this worked, thank you so much for your help -- Louie "Don Guillett" wrote: One way is to put the same formula in the same place on each sheet and then =sum(sheet1:sheet21!a2) -- Don Guillett SalesAid Software "Louie" wrote in message ... I am trying to sum 5 sheets into a master sheet by client using the following formula, I am not sure of how to add all the sheets in the workbook. This is what I got so far for the master sheet =SUMIF(Sheet1!A:A,"approved",Sheet1!C:C) and that works fine for one sheet, how do I apply it so it adds all five sheets at one time for "approved" Please help.! thank you -- Louie |
SUMIF help
I am having this same problem trying to incorporate multiple worksheets into
my SUMIF formula. I can get it to work just fine within one worksheet, but not to include 5 of them. I however can't decipher how I can make this formula work into my document. Can you help? I have all the information in Column C on worksheets 2-6 that needs to match "THHN" and then the sum I want totalled is in Column D on worksheets 2-6 as well. I also have all the worksheets named, so let me know if this is something I need to have changed back to read Sheet 1, Sheet 2 etc. Anything you can do to help - I've tried to use the formula used in SUM to add cell values across multiple worksheets and that didn't work either. Thanks - Shelly "RagDyeR" wrote: To start, you'll need a list of your sheet names. Say in Z1 you enter Sheet1 And drag down to create your Sheet1 to Sheet5 list in Z1 to Z5. Then, try this formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!A:A"),"App roved",INDIRECT("'"&Z1:Z5&"'!C:C"))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Louie" wrote in message ... I am trying to sum 5 sheets into a master sheet by client using the following formula, I am not sure of how to add all the sheets in the workbook. This is what I got so far for the master sheet =SUMIF(Sheet1!A:A,"approved",Sheet1!C:C) and that works fine for one sheet, how do I apply it so it adds all five sheets at one time for "approved" Please help.! thank you -- Louie |
SUMIF help
The formula that Ragdyer posted will do just that
Put all the sheet names in Z1:Z5 (adapt to fit if you have more or less sheets than 5. Change to the correct columns and replace "approve" with your criteria Regards, Peo Sjoblom Shelly wrote: I am having this same problem trying to incorporate multiple worksheets into my SUMIF formula. I can get it to work just fine within one worksheet, but not to include 5 of them. I however can't decipher how I can make this formula work into my document. Can you help? I have all the information in Column C on worksheets 2-6 that needs to match "THHN" and then the sum I want totalled is in Column D on worksheets 2-6 as well. I also have all the worksheets named, so let me know if this is something I need to have changed back to read Sheet 1, Sheet 2 etc. Anything you can do to help - I've tried to use the formula used in SUM to add cell values across multiple worksheets and that didn't work either. Thanks - Shelly "RagDyeR" wrote: To start, you'll need a list of your sheet names. Say in Z1 you enter Sheet1 And drag down to create your Sheet1 to Sheet5 list in Z1 to Z5. Then, try this formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!A:A"),"App roved",INDIRECT("'"&Z1:Z5&"'!C:C"))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Louie" wrote in message ... I am trying to sum 5 sheets into a master sheet by client using the following formula, I am not sure of how to add all the sheets in the workbook. This is what I got so far for the master sheet =SUMIF(Sheet1!A:A,"approved",Sheet1!C:C) and that works fine for one sheet, how do I apply it so it adds all five sheets at one time for "approved" Please help.! thank you -- Louie |
SUMIF help
=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!A:A"),"Ap proved",INDIRECT("'"&Z1:Z5&"'!C:C")))
What are you having a problem with? In the above formula: Z1:Z5 is a range of cells that hold your sheet names. The above formula modified for your situation: =SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!C:C"),"THH N",INDIRECT("'"&Z1:Z5&"'!D:D"))) Biff "Shelly" wrote in message ... I am having this same problem trying to incorporate multiple worksheets into my SUMIF formula. I can get it to work just fine within one worksheet, but not to include 5 of them. I however can't decipher how I can make this formula work into my document. Can you help? I have all the information in Column C on worksheets 2-6 that needs to match "THHN" and then the sum I want totalled is in Column D on worksheets 2-6 as well. I also have all the worksheets named, so let me know if this is something I need to have changed back to read Sheet 1, Sheet 2 etc. Anything you can do to help - I've tried to use the formula used in SUM to add cell values across multiple worksheets and that didn't work either. Thanks - Shelly "RagDyeR" wrote: To start, you'll need a list of your sheet names. Say in Z1 you enter Sheet1 And drag down to create your Sheet1 to Sheet5 list in Z1 to Z5. Then, try this formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z5&"'!A:A"),"App roved",INDIRECT("'"&Z1:Z5&"'!C:C"))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Louie" wrote in message ... I am trying to sum 5 sheets into a master sheet by client using the following formula, I am not sure of how to add all the sheets in the workbook. This is what I got so far for the master sheet =SUMIF(Sheet1!A:A,"approved",Sheet1!C:C) and that works fine for one sheet, how do I apply it so it adds all five sheets at one time for "approved" Please help.! thank you -- Louie |
| All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com