Forum Discussion

srenken's avatar
srenken
Copper Contributor
Oct 14, 2020
Solved

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?

 

  • srenken 

    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

  • srenken 

    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)

Resources