SUMPRODUCT (Again!)

[Connie Martin]

Another simple one, I'm sure. I have this simple formula: =I28+7

I want to add to this that it's to add 14 if "TBG" is found in E28:E500,
otherwise add only 7.

Connie

 

[JE McGimpsey]

One way:

=I28 + 7 + 14*(COUNTIF(E28:E500,"TBG")>0)

If TBG is a partial match, use

"*TBG*"

instead.

 

[Don Guillett]

how about
=l28+if(countif(e28:e500,"TBG")>0,14,7)
or
l28+7+if(countif(e28:e500,"TBG")>0,7,0)

 

[Connie Martin]

The formulas you have given are adding too many days (21 and 14) where TBG is
not in E28:E500.

Perhaps I didn't explain myself well enough. I28 is a date and in M28 I
want a date that's 7 days later than I28, but if E28 says "TBG" then I want
M28 to add 14 days. If E28 says anything else it's to add only 7 days in M28.

Connie

 

[Frank Kabel]

Hi
try
=I28+7+7*(E28="TBG")

 

[Connie Martin]

Thank you! That did it. So, one doesn't use SUMPRODUCT here! No wonder I
get nowhere with these formulas! Ha! Have a nice day, or is it evening
in Germany now? It's just noon here in Canada now.

Connie

 

[Frank Kabel]

Hi Connie
good it works for you
Frank
P.S.: it's around 6pm here in Germany on a quite nice winter day. Time to
buy some presents...

 

[Connie Martin]

Have a safe and happy holiday season. Thank you for all your help this past
year.

Connie Martin
Canada