1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Nested if functions in excel

Discussion in 'Business Applications' started by Ed1967, Apr 29, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Ed1967

    Ed1967 Thread Starter

    Joined:
    Jan 29, 2009
    Messages:
    7
    Well, that's the title, but I'm not even sure that it's the best way of doing what I want to do.

    This is a basic question and I should know the answer, but none of the combinations of functions that I use seem to do the job as I would expect it.

    Basically I have a spreadsheet with a number of columns in it, three of which are year, month and GWP. I need to put these figures into a table (and I've been told I can't use a pivot table, even though it would seem the ideal solution!), to calculate GWP by month and year. I've created named ranges called Month, Year and GWP, and I want something along these lines -

    if(And(month=2)(year=2009),sum(GWP))

    but that just returns "True" when I was expecting, well, a sum of GWP.

    Can anyone help! Do I need to use an array formula, and if so, what is it!?

    Thanks for any assitance.

    Ed
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    I am assuming your dataset spans multiple years and as such you might make a helper column that combines year and month in the same field and use that to group by in your pivottable. Why exactly are you seperating month and year?
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    The way you use the if returns the comparisson
    if month=2 and year = 2009 then sum(GWP)

    I assume it's in Excel?

    the other option is: using the activecell.value = IIF(month=2 and year=2009,sum(GWP),"")
    This is an implicit IF statement is the first part is true then the sum else blank

    I hope it this gives you some help
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Ed, can you post a copy of your file (with some fake data if needed) so I can tinker with it? I am not great with named ranges but can't see why
    =if(And(month=2,year=2009),sum(GWP),"")
    would not work.
     
  5. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    That's a good thinking, Jim. My first thought was the same. But the OP said pivottable was not an option. We should use SUMIF instead.

    Jimmy
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/920000

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice