Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Worksheet Functions / October 2007

Tip: Looking for answers? Try searching our database.

Sumif

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steve - 09 Oct 2007 16:40 GMT
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!
Reply to this Message
Pete_UK - 09 Oct 2007 17:11 GMT
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

> 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!
Reply to this Message
Steve - 09 Oct 2007 17:39 GMT
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!!

> 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
[quoted text clipped - 16 lines]
>
> - Show quoted text -
Reply to this Message
T. Valko - 09 Oct 2007 18:25 GMT
Try this:

=SUMIF(Sheet2!B3:B18000,Sheet1!A3,INDEX(Sheet2!N3:DD18000,,MATCH(Sheet1!A1,Sheet2!N2:DD2,0)))

Signature

Biff
Microsoft Excel MVP

> Hi Pete,  thanks for the response.  But I don't think I was very clear
> in explaining my problem.  Let me try again:
[quoted text clipped - 31 lines]
>>
>> - Show quoted text -
Reply to this Message
Steve - 09 Oct 2007 18:44 GMT
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?

> Try this:
>
[quoted text clipped - 41 lines]
>
> - Show quoted text -
Reply to this Message
T. Valko - 09 Oct 2007 19:04 GMT
Try this:

=SUMPRODUCT(--(Sheet2!B3:B18000=Sheet1!A3),INDEX(Sheet2!N3:DD18000,,MATCH(Sheet1!A1,Sheet2!N2:DD2,0))*Sheet1!CC3:CC18000)

You didn't say what sheet column CC was on so I used Sheet1.

Signature

Biff
Microsoft Excel MVP

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" <biffinp...@comcast.net> wrote:
> Try this:
>
[quoted text clipped - 48 lines]
>
> - Show quoted text -
Reply to this Message
Steve - 09 Oct 2007 19:48 GMT
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!

> Try this:
>
[quoted text clipped - 68 lines]
>
> - Show quoted text -
Reply to this Message
T. Valko - 09 Oct 2007 22:02 GMT
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.

Signature

Biff
Microsoft Excel MVP

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" <biffinp...@comcast.net> wrote:
> Try this:
>
[quoted text clipped - 68 lines]
>
> - Show quoted text -
Reply to this Message
Steve - 09 Oct 2007 22:28 GMT
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,Sheet2!$CD
$2:$CD$18000)

> Let's see if I understand what you wanted to do...
>
[quoted text clipped - 91 lines]
>
> - Show quoted text -
Reply to this Message
T. Valko - 09 Oct 2007 22:46 GMT
Ok, syntactically, there's nothing wrong with that test formula. Are there
any errors in any of your ranges?

Signature

Biff
Microsoft Excel MVP

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,Sheet2!$CD
$2:$CD$18000)

On Oct 9, 3:02 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> Let's see if I understand what you wanted to do...
>
[quoted text clipped - 107 lines]
>
> - Show quoted text -
Reply to this Message
Steve - 09 Oct 2007 22:37 GMT
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!!!

> Let's see if I understand what you wanted to do...
>
[quoted text clipped - 91 lines]
>
> - Show quoted text -
Reply to this Message
T. Valko - 09 Oct 2007 22:53 GMT
Good deal. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

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" <biffinp...@comcast.net> wrote:
> Let's see if I understand what you wanted to do...
>
[quoted text clipped - 107 lines]
>
> - Show quoted text -
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.