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

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
 
Joined
Jul 20, 2007
Messages
4,193
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?
 

Keebellah

Trusted Advisor
Joined
Mar 27, 2008
Messages
6,611
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
 
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.
 
Joined
Jul 28, 2006
Messages
1,223
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top