• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

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?
  • 2
1 Solution
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")
barry houdiniCommented:
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
jmac001Author Commented:

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


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
barry houdiniCommented:
>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
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now