using sumproduct with a substring condition?

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I have a calculation I just made that looks like this:
=SUMPRODUCT(--(C5:C50="persons name"),I5:I50)
which works out the hours billed from column I and works fine.

Column E has a description in it which sometimes has at the end of the
description the words "do not bill." Is there any way to add this
condition to the sumproduct - i.e. exclude from the sumproduct any value
in column I that has the "do not bill" sub-text in column e?

Thanks,
Ben
 
exclude from the sumproduct any value
in column I that has the "do not bill"
Click to expand...

Try it like this, all on one line...

=SUMPRODUCT(--(C5:C50="persons name"),
--(ISERROR(SEARCH("do not bill",E5:E50))),I5:I50)
 
Back
Top