Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summing Sheets - SUMIF ?
I have a workbook where I want to be able to exclude the cells in the
summing of one or more sheets based on some logic. The solution needs to be a dynamic formula based on a flag such as Yes or No (include or exclude the sheet) eg how could I vary the formula below if I didnt want to get the values of A1 from Sheet5 and Sheet9 in the summed total =SUM(Sheet2:Sheet11!A1) is there some smart variation of SUMIF that could apply that would refer to say a table of Sheet names with a Yes / No flag next to each Sheet name? Steve W |
#2
|
|||
|
|||
One approx way might be to set it up along these lines:
Listed across in B1:F1 are the sheetnames: Sheet1, Sheet2 ... Sheet5 In the row beneath the sheetnames, in B2:F2 are data validations (DVs) to select "Yes" or "No" To set up the DVs, just select the range B2:F2, Click Data Validation, select List (under "Allow") Put in the source box: Yes, No Click OK Listed in cell A3 down would be the cell references to be summed in the sheets, e.g.: A1, A2, etc Put in B3: =IF(B2="Yes",INDIRECT(B1&"!"&$A3),"") Copy across to F3 Put in G3: =SUM(B3:F3) Select B3:G3, fill down Cols B to F will extract the values for the cell refs in col A from the sheets flagged "Yes" in B2:F2 and Col G will give the required sums of these values -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Steve W" wrote in message ... I have a workbook where I want to be able to exclude the cells in the summing of one or more sheets based on some logic. The solution needs to be a dynamic formula based on a flag such as Yes or No (include or exclude the sheet) eg how could I vary the formula below if I didnt want to get the values of A1 from Sheet5 and Sheet9 in the summed total =SUM(Sheet2:Sheet11!A1) is there some smart variation of SUMIF that could apply that would refer to say a table of Sheet names with a Yes / No flag next to each Sheet name? Steve W |
#3
|
|||
|
|||
Sorry, correction* for formula in B3 in:
Put in B3: =IF(B2="Yes",INDIRECT(B1&"!"&$A3),"") Copy across to F3 Formula should read as: Put in B3: =IF(B$2="Yes",INDIRECT(B$1&"!"&$A3),"") *cell refs B2 and B1 corrected to enable copy down later -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
Assuming that your sheets are named Sheet1, Sheet2, etc., and that
B1:B10 contains your sheet names, and C1:C10 contains the corresponding flag, Yes/No, try the following formula... =SUM(SUMIF(INDIRECT("'Sheet"&SMALL(IF(C1:C10="Yes" ,ROW(C1:C10)),ROW(INDIR ECT("1:"&COUNTIF(C1:C10,"Yes"))))&"'!A1"),"<")) Hope this helps! In article , "Steve W" wrote: I have a workbook where I want to be able to exclude the cells in the summing of one or more sheets based on some logic. The solution needs to be a dynamic formula based on a flag such as Yes or No (include or exclude the sheet) eg how could I vary the formula below if I didnt want to get the values of A1 from Sheet5 and Sheet9 in the summed total =SUM(Sheet2:Sheet11!A1) is there some smart variation of SUMIF that could apply that would refer to say a table of Sheet names with a Yes / No flag next to each Sheet name? Steve W |
#5
|
|||
|
|||
In article ,
Domenic wrote: Assuming that your sheets are named Sheet1, Sheet2, etc., and that B1:B10 contains your sheet names, and C1:C10 contains the corresponding flag, Yes/No, try the following formula... =SUM(SUMIF(INDIRECT("'Sheet"&SMALL(IF(C1:C10="Yes" ,ROW(C1:C10)),ROW(INDIR ECT("1:"&COUNTIF(C1:C10,"Yes"))))&"'!A1"),"<")) Hope this helps! I forgot to mention that the formula must be confirmed with CONTROL+SHIFT+ENTER, and not just ENTER. Hope this helps! |
#6
|
|||
|
|||
If your sheets are in fact not named Sheet1, Sheet2, etc., try the
following instead... =SUM(SUMIF(INDIRECT("'"&T(OFFSET(B1,SMALL(IF(C1:C1 0="Yes",ROW(C1:C10)),RO W(INDIRECT("1:"&D1)))-CELL("row",E1),0))&"'!A1"),"<")) ....confirmed with CONTROL+SHIFT+ENTER, where B1:B10 contains your sheet names, C1:C10 contains your flag, Yes/No, and D1 contains the following formula... =COUNTIF(C1:C10,"Yes") Hope this helps! In article , "Steve W" wrote: I have a workbook where I want to be able to exclude the cells in the summing of one or more sheets based on some logic. The solution needs to be a dynamic formula based on a flag such as Yes or No (include or exclude the sheet) eg how could I vary the formula below if I didnt want to get the values of A1 from Sheet5 and Sheet9 in the summed total =SUM(Sheet2:Sheet11!A1) is there some smart variation of SUMIF that could apply that would refer to say a table of Sheet names with a Yes / No flag next to each Sheet name? Steve W |
#7
|
|||
|
|||
Can see how this works really well BUT, what if you wanted to apply this
formula to all possible cells in your specified worksheet array rather than just A1. How would/could you alter the formula to allow the A1 reference at the end to become dynamic when copied. Steve "Domenic" wrote in message ... If your sheets are in fact not named Sheet1, Sheet2, etc., try the following instead... =SUM(SUMIF(INDIRECT("'"&T(OFFSET(B1,SMALL(IF(C1:C1 0="Yes",ROW(C1:C10)),RO W(INDIRECT("1:"&D1)))-CELL("row",E1),0))&"'!A1"),"<")) ...confirmed with CONTROL+SHIFT+ENTER, where B1:B10 contains your sheet names, C1:C10 contains your flag, Yes/No, and D1 contains the following formula... =COUNTIF(C1:C10,"Yes") Hope this helps! In article , "Steve W" wrote: I have a workbook where I want to be able to exclude the cells in the summing of one or more sheets based on some logic. The solution needs to be a dynamic formula based on a flag such as Yes or No (include or exclude the sheet) eg how could I vary the formula below if I didnt want to get the values of A1 from Sheet5 and Sheet9 in the summed total =SUM(Sheet2:Sheet11!A1) is there some smart variation of SUMIF that could apply that would refer to say a table of Sheet names with a Yes / No flag next to each Sheet name? Steve W |
#8
|
|||
|
|||
Try the following...
=SUM(SUMIF(INDIRECT("'"&T(OFFSET($B$1,SMALL(IF($C$ 1:$C$10="Yes",ROW($C$1: $C$10)),ROW(INDIRECT("1:"&$D$1)))-CELL("row",$E$1),0))&"'!"&CELL("address ",A1)),"<")) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "Steve W" wrote: Can see how this works really well BUT, what if you wanted to apply this formula to all possible cells in your specified worksheet array rather than just A1. How would/could you alter the formula to allow the A1 reference at the end to become dynamic when copied. Steve "Domenic" wrote in message ... If your sheets are in fact not named Sheet1, Sheet2, etc., try the following instead... =SUM(SUMIF(INDIRECT("'"&T(OFFSET(B1,SMALL(IF(C1:C1 0="Yes",ROW(C1:C10)),RO W(INDIRECT("1:"&D1)))-CELL("row",E1),0))&"'!A1"),"<")) ...confirmed with CONTROL+SHIFT+ENTER, where B1:B10 contains your sheet names, C1:C10 contains your flag, Yes/No, and D1 contains the following formula... =COUNTIF(C1:C10,"Yes") Hope this helps! In article , "Steve W" wrote: I have a workbook where I want to be able to exclude the cells in the summing of one or more sheets based on some logic. The solution needs to be a dynamic formula based on a flag such as Yes or No (include or exclude the sheet) eg how could I vary the formula below if I didnt want to get the values of A1 from Sheet5 and Sheet9 in the summed total =SUM(Sheet2:Sheet11!A1) is there some smart variation of SUMIF that could apply that would refer to say a table of Sheet names with a Yes / No flag next to each Sheet name? Steve W |
#9
|
|||
|
|||
See also my contrib in:
http://www.excelforum.com//showthread.php?t=328999 which seems not to arrive at ms news group servers. "Steve W" wrote in message ... I have a workbook where I want to be able to exclude the cells in the summing of one or more sheets based on some logic. The solution needs to be a dynamic formula based on a flag such as Yes or No (include or exclude the sheet) eg how could I vary the formula below if I didnt want to get the values of A1 from Sheet5 and Sheet9 in the summed total =SUM(Sheet2:Sheet11!A1) is there some smart variation of SUMIF that could apply that would refer to say a table of Sheet names with a Yes / No flag next to each Sheet name? Steve W |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
SUMIF and 3-D Ranges | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions | |||
sumif to add data in multiple sheets | Excel Worksheet Functions |