• Status: Solved
• Priority: Medium
• Security: Public
• Views: 644

# SUMIF with multiple conditions

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
jmac001
• 2
1 Solution

Commented:
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

Commented:
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 Commented:
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

Commented:
>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.