|
Sumif
Hi all, is there a way to write a sumif formula where the column of
the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks! |
Sumif
Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc)
were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks! |
Sumif
Hi Pete, thanks for the response. But I don't think I was very clear
in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text - |
Sumif
Try this:
=SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message ups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text - |
Sumif
Thanks Biff. Works awesome! I ran into another issue somewhat
related. There is one set of data that requires me to multiply the found criteria by the percentage in column CC of the same row where the criteria was found. So the Sumif formula applies, but can we modifyit to be a sum-if-product? On Oct 9, 11:25 am, "T. Valko" wrote: Try this: =SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,*Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message ups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Sumif
Try this:
=SUMPRODUCT(--(Sheet2!B3:B18000=Sheet1!A3),INDEX(Sheet2!N3:DD180 00,,MATCH(Sheet1!A1,Sheet2!N2:DD2,0))*Sheet1!CC3:C C18000) You didn't say what sheet column CC was on so I used Sheet1. -- Biff Microsoft Excel MVP "Steve" wrote in message ps.com... Thanks Biff. Works awesome! I ran into another issue somewhat related. There is one set of data that requires me to multiply the found criteria by the percentage in column CC of the same row where the criteria was found. So the Sumif formula applies, but can we modifyit to be a sum-if-product? On Oct 9, 11:25 am, "T. Valko" wrote: Try this: =SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,*Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message ups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Sumif
Hey Biff. I'dl love to say that worked, but unfortunatelyI got an #N/
A error. And again unfortunately, I don't understand that formula enough (really at all!) to be able to find the source of the error. I tried, but came up with nothing. Any ideas? Thanks for your help! On Oct 9, 12:04 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!B3:B18000=Sheet1!A3),INDEX(Sheet2!N3:DD180 00,,MATCH(S*heet1!A1,Sheet2!N2:DD2,0))*Sheet1!CC3: CC18000) You didn't say what sheet column CC was on so I used Sheet1. -- Biff Microsoft Excel MVP "Steve" wrote in message ps.com... Thanks Biff. Works awesome! I ran into another issue somewhat related. There is one set of data that requires me to multiply the found criteria by the percentage in column CC of the same row where the criteria was found. So the Sumif formula applies, but can we modifyit to be a sum-if-product? On Oct 9, 11:25 am, "T. Valko" wrote: Try this: =SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,**Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Sumif
Let's see if I understand what you wanted to do...
Sum If Sheet2!B3:B18000 = Sheet1!A3 The column of values to sum are found in Sheet2!N3:DD18000 based on a matching date from S*heet1!A1 and Sheet2!N2:DD2. Multiply those values by a percentage from C3:C18000 on the same row and sum the total. -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hey Biff. I'dl love to say that worked, but unfortunatelyI got an #N/ A error. And again unfortunately, I don't understand that formula enough (really at all!) to be able to find the source of the error. I tried, but came up with nothing. Any ideas? Thanks for your help! On Oct 9, 12:04 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!B3:B18000=Sheet1!A3),INDEX(Sheet2!N3:DD180 00,,MATCH(S*heet1!A1,Sheet2!N2:DD2,0))*Sheet1!CC3: CC18000) You didn't say what sheet column CC was on so I used Sheet1. -- Biff Microsoft Excel MVP "Steve" wrote in message ps.com... Thanks Biff. Works awesome! I ran into another issue somewhat related. There is one set of data that requires me to multiply the found criteria by the percentage in column CC of the same row where the criteria was found. So the Sumif formula applies, but can we modifyit to be a sum-if-product? On Oct 9, 11:25 am, "T. Valko" wrote: Try this: =SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,**Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Sumif
Exactly! (except you said it far mre eloquently than I did!)
I simplified the formula significantly to test by removing the entire index piece and simply using a column array in sheet 2, and got the same error. Here's the formula I tested: =SUMPRODUCT(--(Sheet2!$B$2:$B$18000=A3),Sheet2!CE2:CE18000,Sheet 2!$CD $2:$CD$18000) On Oct 9, 3:02 pm, "T. Valko" wrote: Let's see if I understand what you wanted to do... Sum If Sheet2!B3:B18000 = Sheet1!A3 The column of values to sum are found in Sheet2!N3:DD18000 based on a matching date from S*heet1!A1 and Sheet2!N2:DD2. Multiply those values by a percentage from C3:C18000 on the same row and sum the total. -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hey Biff. I'dl love to say that worked, but unfortunatelyI got an #N/ A error. And again unfortunately, I don't understand that formula enough (really at all!) to be able to find the source of the error. I tried, but came up with nothing. Any ideas? Thanks for your help! On Oct 9, 12:04 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!B3:B18000=Sheet1!A3),INDEX(Sheet2!N3:DD180 00,,MATCH(S**heet1!A1,Sheet2!N2:DD2,0))*Sheet1!CC3 :CC18000) You didn't say what sheet column CC was on so I used Sheet1. -- Biff Microsoft Excel MVP "Steve" wrote in message ups.com... Thanks Biff. Works awesome! I ran into another issue somewhat related. There is one set of data that requires me to multiply the found criteria by the percentage in column CC of the same row where the criteria was found. So the Sumif formula applies, but can we modifyit to be a sum-if-product? On Oct 9, 11:25 am, "T. Valko" wrote: Try this: =SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,***Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Sumif
Biff,
I think I got it. Some of the data in the array were #Value errors from a lookup. Even though the cells with errors never passed the "if" test, it still messed up the formula. So when i added an iserror formula to them, the #N/A on your formula went away. Thank you so much for your help!!! On Oct 9, 3:02 pm, "T. Valko" wrote: Let's see if I understand what you wanted to do... Sum If Sheet2!B3:B18000 = Sheet1!A3 The column of values to sum are found in Sheet2!N3:DD18000 based on a matching date from S*heet1!A1 and Sheet2!N2:DD2. Multiply those values by a percentage from C3:C18000 on the same row and sum the total. -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hey Biff. I'dl love to say that worked, but unfortunatelyI got an #N/ A error. And again unfortunately, I don't understand that formula enough (really at all!) to be able to find the source of the error. I tried, but came up with nothing. Any ideas? Thanks for your help! On Oct 9, 12:04 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!B3:B18000=Sheet1!A3),INDEX(Sheet2!N3:DD180 00,,MATCH(S**heet1!A1,Sheet2!N2:DD2,0))*Sheet1!CC3 :CC18000) You didn't say what sheet column CC was on so I used Sheet1. -- Biff Microsoft Excel MVP "Steve" wrote in message ups.com... Thanks Biff. Works awesome! I ran into another issue somewhat related. There is one set of data that requires me to multiply the found criteria by the percentage in column CC of the same row where the criteria was found. So the Sumif formula applies, but can we modifyit to be a sum-if-product? On Oct 9, 11:25 am, "T. Valko" wrote: Try this: =SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,***Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Sumif
Ok, syntactically, there's nothing wrong with that test formula. Are there
any errors in any of your ranges? -- Biff Microsoft Excel MVP "Steve" wrote in message ups.com... Exactly! (except you said it far mre eloquently than I did!) I simplified the formula significantly to test by removing the entire index piece and simply using a column array in sheet 2, and got the same error. Here's the formula I tested: =SUMPRODUCT(--(Sheet2!$B$2:$B$18000=A3),Sheet2!CE2:CE18000,Sheet 2!$CD $2:$CD$18000) On Oct 9, 3:02 pm, "T. Valko" wrote: Let's see if I understand what you wanted to do... Sum If Sheet2!B3:B18000 = Sheet1!A3 The column of values to sum are found in Sheet2!N3:DD18000 based on a matching date from S*heet1!A1 and Sheet2!N2:DD2. Multiply those values by a percentage from C3:C18000 on the same row and sum the total. -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hey Biff. I'dl love to say that worked, but unfortunatelyI got an #N/ A error. And again unfortunately, I don't understand that formula enough (really at all!) to be able to find the source of the error. I tried, but came up with nothing. Any ideas? Thanks for your help! On Oct 9, 12:04 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!B3:B18000=Sheet1!A3),INDEX(Sheet2!N3:DD180 00,,MATCH(S**heet1!A1,Sheet2!N2:DD2,0))*Sheet1!CC3 :CC18000) You didn't say what sheet column CC was on so I used Sheet1. -- Biff Microsoft Excel MVP "Steve" wrote in message ups.com... Thanks Biff. Works awesome! I ran into another issue somewhat related. There is one set of data that requires me to multiply the found criteria by the percentage in column CC of the same row where the criteria was found. So the Sumif formula applies, but can we modifyit to be a sum-if-product? On Oct 9, 11:25 am, "T. Valko" wrote: Try this: =SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,***Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Sumif
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Steve" wrote in message ps.com... Biff, I think I got it. Some of the data in the array were #Value errors from a lookup. Even though the cells with errors never passed the "if" test, it still messed up the formula. So when i added an iserror formula to them, the #N/A on your formula went away. Thank you so much for your help!!! On Oct 9, 3:02 pm, "T. Valko" wrote: Let's see if I understand what you wanted to do... Sum If Sheet2!B3:B18000 = Sheet1!A3 The column of values to sum are found in Sheet2!N3:DD18000 based on a matching date from S*heet1!A1 and Sheet2!N2:DD2. Multiply those values by a percentage from C3:C18000 on the same row and sum the total. -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hey Biff. I'dl love to say that worked, but unfortunatelyI got an #N/ A error. And again unfortunately, I don't understand that formula enough (really at all!) to be able to find the source of the error. I tried, but came up with nothing. Any ideas? Thanks for your help! On Oct 9, 12:04 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet2!B3:B18000=Sheet1!A3),INDEX(Sheet2!N3:DD180 00,,MATCH(S**heet1!A1,Sheet2!N2:DD2,0))*Sheet1!CC3 :CC18000) You didn't say what sheet column CC was on so I used Sheet1. -- Biff Microsoft Excel MVP "Steve" wrote in message ups.com... Thanks Biff. Works awesome! I ran into another issue somewhat related. There is one set of data that requires me to multiply the found criteria by the percentage in column CC of the same row where the criteria was found. So the Sumif formula applies, but can we modifyit to be a sum-if-product? On Oct 9, 11:25 am, "T. Valko" wrote: Try this: =SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3: DD18000,,MATCH(Sheet1!A1,***Sheet2!N2:DD2,0))) -- Biff Microsoft Excel MVP "Steve" wrote in message oups.com... Hi Pete, thanks for the response. But I don't think I was very clear in explaining my problem. Let me try again: In cell A1of Sheet1 I have a pulldown menu of Dates (1/1/2007 through 12/1/2009). On Sheet2, Cells N2 though DD2 have dates. I'm trying to use a standard Sumif, with the column to search being Sheet2! B3:B18000, the criteria to find in Sheet1!A3, BUT the column to sum varying based on the criteria selected in Sheet1!A1. Essentially, I need it to find a "match" based on the contents of Sheet1!A1. For instance, if the user selects 8/1/2008 in cell A1, then the Sumif will look for 8/1/2008 in Sheet2!N2:DD2 and sum the column that it finds it in. Does that make more sense? Thanks for your help!! On Oct 9, 10:11 am, Pete_UK wrote: Here's one way of doing it. I assumed your months (Jan, Feb, Mar etc) were in B2:M2, with data immediately below them in row3. Put this formula in A3: =SUM(INDIRECT("B3:"&CHAR(MATCH(A1,B2:M2,0)+65)&"3" )) Then choose your month from the pull-down in A1 and you will have the sum from January up to the month chosen. Hope this helps. Pete On Oct 9, 4:40 pm, Steve wrote: Hi all, is there a way to write a sumif formula where the column of the data to be summed is a variable? In cell A1, I have a pulldown menu of Months. Across row 2, I have the months Jan-Dec. I'm trying to vary the column to be summed based on the month selected in A1. Possible? Thanks!- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 06:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com