Forum Discussion
Add another column of criteria to SUMIF
I have inherited an excellent workbook and now I would like to add another column to my SUMIF criteria.
I've tried a few different ways and keep getting formula errors about too many arguments, or missing parenthesis and haven't been able to figure it out.
What I would like to add another total where the formula would sumif from the MAIN DATA worksheet using column E if the entry in column E is "1". If column M, Q, or U matched Building and column E matched 1 (1st quarter), the revenue in column AE would be totaled.
Here is the formula before adding column E criteria to the mix:
=SUMIF('MAIN DATA'!$M$2:$M$798,"Building",'MAIN DATA'!$AE$2:$AE$798)+SUMIF('MAIN DATA'!$Q$2:$Q$798,"Building",'MAIN DATA'!$T$2:$T$798)+SUMIF('MAIN DATA'!$U$2:$U$798,"Building",'MAIN DATA'!$X$2:$X$798)
Where do I need to put the criteria for column E?
If you have multiple criteria, use SUMIFS instead of SUMIF. In SUMIFS, the sum range is the first argument:
=SUMIF('MAIN DATA'!$AE$2:$AE$798,'MAIN DATA'!$M$2:$M$798,"Building",'MAIN DATA'!$E$2:$E$798,1)+SUMIF('MAIN DATA'!$T$2:$T$798,'MAIN DATA'!$Q$2:$Q$798,"Building",'MAIN DATA'!$E$2:$E$798,1)+SUMIF('MAIN DATA'!$X$2:$X$798,'MAIN DATA'!$U$2:$U$798,"Building",'MAIN DATA'!$E$2:$E$798,1)
3 Replies
If you have multiple criteria, use SUMIFS instead of SUMIF. In SUMIFS, the sum range is the first argument:
=SUMIF('MAIN DATA'!$AE$2:$AE$798,'MAIN DATA'!$M$2:$M$798,"Building",'MAIN DATA'!$E$2:$E$798,1)+SUMIF('MAIN DATA'!$T$2:$T$798,'MAIN DATA'!$Q$2:$Q$798,"Building",'MAIN DATA'!$E$2:$E$798,1)+SUMIF('MAIN DATA'!$X$2:$X$798,'MAIN DATA'!$U$2:$U$798,"Building",'MAIN DATA'!$E$2:$E$798,1)
- srenkenCopper Contributor
HansVogelaarThank you...I have this working now!!
- srenkenCopper Contributor
Thanks, I will give that a try!