# Nested if functions in excel

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

Thread Status:
Not open for further replies.
Advertisement
1. ### Ed1967Thread 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

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. ### KeebellahTrusted 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

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

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

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.

over 733,556 other people just like you!

Thread Status:
Not open for further replies.

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