how to add additional criteria to a SUMIF formual?

**jgomez** · 07-14-2011, 12:08 PM

I got this formula that works like a charm…

=SUMIF('Commercial Loans'!J3:J100,"*AL*",'Commercial Loans'!G3:G100)

I’d like to add to it but don’t know how to go about it…

I would like for it to sum anything that contains “*AL*” but only if cell X3(commercial loans tab) is less than or equal to cell C41(summary tab).

**jgomez** · 07-14-2011, 01:02 PM

I tired a sumifs:

=SUMIFS('Commercial Loans'!G3:G100,'Commercial Loans'!X3:X100,">=Summary!C41",'Commercial Loans'!J3:J100,"*AL*")
and it returns zero...

**jgomez** · 07-14-2011, 01:23 PM

Finally getting somewhere

I got this:

=SUMPRODUCT(--(Summary!C41<='Commercial Loans'!X3:X100)--('Commercial Loans'!J3:J100="Iris"),'Commercial Loans'!G3:G100)

and it returns 1,018,396.90
but it should only bring 693,609.18 over... any ideas why?

**jgomez** · 07-14-2011, 02:51 PM

I know there hasn't been any replies just yet but when I get a count i also get a wrong answer...

**Colin Legg** · 07-14-2011, 03:53 PM

but only if cell X3(commercial loans tab) is less than or equal to cell C41(summary tab).

When you're using SUMIF, SUMIFS, etc, you have to concatenate the <= condition with the cell reference, like this:

=SUMIFS('Commercial Loans'!G3:G100,'Commercial Loans'!X3:X100, "<="&Summary!C41,'Commercial Loans'!J3:J100,"*AL*")

I noted that in your post you said "less than or equal to C41", but in your attempted SUMIFS formula, you tried to use >=C41.