# Nested if functions in excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### Ed1967

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

#### jimr381

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?

#### Keebellah

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

#### slurpee55

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.

#### Jimmy the Hand

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?
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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On

### Welcome to Tech Support Guy!

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

over 807,865 other people just like you!