jmac001
asked on
SUMIF with multiple conditions
I have the following sumif that is working:
=SUMIF('Schedule Variance Data'!$J$2:$J$26,"landlord *",'Schedu le Variance Data'!$I$2:$J$26)+SUMIF('S chedule Variance Data'!$N$2:$N$26,"landlord *",'Schedu le Variance Data'!$M$2:$N26)+SUMIF('Sc hedule Variance Data'!$R$2:$R$26,"landlord *",'Schedu le Variance Data'!$Q$2:$R$26)+SUMIF('S chedule Variance Data'!$V$2:$V$26,"landlord *",'Schedu le Variance Data'!$U$2:$V$26)+SUMIF('S chedule Variance Data'!$Z$2:$Z$26,"landlord *",'Schedu le Variance Data'!$Y$2:$Z$26)+SUMIF('S chedule Variance Data'!$AD$2:$AD$26,"landlo rd*",'Sche dule Variance Data'!$AC$2:$AD$26)+SUMIF( 'Schedule Variance Data'!$AH$2:$AH$26,"landlo rd*",'Sche dule Variance Data'!$AG$2:$AH$26)+SUMIF( 'Schedule Variance Data'!$AL$2:$AL$26,"landlo rd*",'Sche dule Variance Data'!$AK$2:$AL$26)
I need to add the condition of Column E = OPN, can this be done?
=SUMIF('Schedule Variance Data'!$J$2:$J$26,"landlord
I need to add the condition of Column E = OPN, can this be done?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
>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
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
=SUMPRODUCT((LEFT('Schedul
and then the extra condition can be easily added like this:
=SUMPRODUCT((LEFT('Schedul
regards, barry