Sumif

Kyle Lewis

Hi, 

I am trying to get the sumif function to work for me and I am having trouble. I am trying to count the dollar amount of invoices in column Invoice Amount if the the JOB# column has "BO" in it. 

These are the formulas I have tried so far:

=SUMIF([JOB#]:[JOB#], IF(FIND("BO", [JOB#]:[JOB#]), [Invoice Amount]:[Invoice Amount]))

=SUMIFS([Invoice Amount]:[Invoice Amount], AND(FIND("BO", [JOB#]:[JOB#], @cell)), [Invoice Amount]:[Invoice Amount]))

=SUMIFS([Invoice Amount]:[Invoice Amount], AND(FIND("BO", [JOB#]:[JOB#], @cell)),[JOB#]:[JOB#] ))

=SUMIF([JOB#]:[JOB#], FIND("BO", [JOB#]:[JOB#]), [Invoice Amount]:[Invoice Amount]))

 

The first one returns 0 and the rest #unparseable. I should be getting $1000

Attached are the columns I need to use, and they are on the same page as my calculations. 

Kyle Lewis

Forgot to add that I will have other job#s that start with different characters which is why I need to isolate certain jobs against the correct invoice amount. 

Mike Wilday

This should work for you... 

=SUMIF([Job #]:[Job #], Find("BO", @cell) > 0, [Invoice Amount]:[Invoice Amount])

You were missing the criteria of > 0. 

Let me know if it works! 

Kyle Lewis

That worked! 

 

Thanks!