using sumproduct with a substring condition?

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
 
T

T. Valko

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct Help 1
Sumproduct function allow a lookup for one Date to check if it fallsin Date Range in other table 9
Look Up Formula With Veried Search Range 2
Counting cells with SUMPRODUCT and conditionals...? 1
IF, AND & OR 3
Sumproduct & Dates & Multiple similar alpha Data 5
Sumproduct when data column changes 1
How to find matches, pairs of two SUMPRODUCT? 7

Top