Solved

SUMIF with multiple conditions

Posted on 2013-06-25
4
573 Views
Last Modified: 2013-06-27
I have the following sumif that is working:

=SUMIF('Schedule Variance Data'!$J$2:$J$26,"landlord*",'Schedule Variance Data'!$I$2:$J$26)+SUMIF('Schedule Variance Data'!$N$2:$N$26,"landlord*",'Schedule Variance Data'!$M$2:$N26)+SUMIF('Schedule Variance Data'!$R$2:$R$26,"landlord*",'Schedule Variance Data'!$Q$2:$R$26)+SUMIF('Schedule Variance Data'!$V$2:$V$26,"landlord*",'Schedule Variance Data'!$U$2:$V$26)+SUMIF('Schedule Variance Data'!$Z$2:$Z$26,"landlord*",'Schedule Variance Data'!$Y$2:$Z$26)+SUMIF('Schedule Variance Data'!$AD$2:$AD$26,"landlord*",'Schedule Variance Data'!$AC$2:$AD$26)+SUMIF('Schedule Variance Data'!$AH$2:$AH$26,"landlord*",'Schedule Variance Data'!$AG$2:$AH$26)+SUMIF('Schedule Variance Data'!$AL$2:$AL$26,"landlord*",'Schedule Variance Data'!$AK$2:$AL$26)

I need to add the condition of Column E = OPN, can this be done?
0
Comment
Question by:jmac001
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 39276316
Are you using Pre Excel 2003 or 2007+ ?
Also your current formula has errors:
=SUMIF('Schedule Variance Data'!$J$2:$J$26,"landlord*",'Schedule Variance Data'!$I$2:$J$26)
will only sum up column I2:I26 not J too. It should be
=SUMIF('Schedule Variance Data'!$J$2:$J$26,"landlord*",'Schedule Variance Data'!$I$2:$I$26)

If using 2007+ you can change to SUMIFS
=SUMIFS('Schedule Variance Data'!$I$2:$I$26, 'Schedule Variance Data'!$J$2:$J$26,"landlord*", 'Schedule Variance Data'!$E$2:$E$26, "OPN")
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39277794
Is there anything in your existing sheet that would indicate which columns are the "landlord" columns? For example if the header row says "Title" in columns J, N, R, V, Z, AD, AH and AL then you could reduce your current long formula to the following for the whole thing:

=SUMPRODUCT((LEFT('Schedule Variance Data'!$J$2:$AL$26,8)="landlord")*('Schedule Variance Data'!$J$1:$AL$1="Title"),'Schedule Variance Data'!$I$2:$AK$26)

and then the extra condition can be easily added like this:

=SUMPRODUCT((LEFT('Schedule Variance Data'!$J$2:$AL$26,8)="landlord")*('Schedule Variance Data'!$J$1:$AL$1="Title")*('Schedule Variance Data'!$E$2:$E$26="OPN"),'Schedule Variance Data'!$I$2:$AK$26)

regards, barry
0
 

Author Comment

by:jmac001
ID: 39281185
Barry,

The information was scatter throughout the columns, wish it would have been as easy as using the header.

BarMan,

The solution for 2007+ worked wonderfully, I've tried using the SUMIFS function in the past but without success.  With your example I was able to use the countifs function as well. -- Thanks
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39282902
>The information was scatter throughout the columns, wish it would have been as easy as using the header.

OK, I know the question is answered, The_Barman has a good working solution but I think you could substantially reduce the size of that formula, if you're interested.....

I'm sure that the "landlord" entries are scattered through the columns.......but my point was about identifying the specific columns that are used. You are only looking for "landlord*" in a limited number of columns, if you already have a header which can identify those columns then you can use a version of my suggested formula, or you could amend the headers to suit.

What is currently in 'Schedule Variance Data'!$J$1, 'Schedule Variance Data'!$N$1,'Schedule Variance Data'!$R$1 etc.? If those header values have a common theme then yo can use a version of the formula I suggested.

If you can post a version of your sheet I suspect I could modify the formula I suggested to suit

regards, barry
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question