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!

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!

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 -

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 -

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 -

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 -