Solved

SUMIF with multiple conditions

Posted on 2013-06-25
4
541 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now