Inserting Text If Other Conditions Are Met

[elitarvin]

I have received some great help from you guys for this workbook. However I need one more thing done. I can't seem to piece it together using the other formulas I received from you guys.

What I would like is for example, if any cell in column C on "Sales and Use Tax" contains "John Smith" AND contains either "Weekly Cleaning Service Renewal" OR "Biweekly Cleaning Service Renewal" in column D, I would like it to insert "Yes" into column Q on sheet "Payments - Cleaning Service" in the row that belongs to John Smith. So if he is in row R, insert "Yes" into column Q row R on "Payments - Cleaning Service" IF John Smith is in column C on sheet "Sales and Use Tax" AND either "Weekly Cleaning Service Renewal" OR "Biweekly Cleaning Service Renewal" is in column D.

Thanks so much in advance!

[JohnTopley]

Try

in Q7

=IFERROR(IF(MATCH(1,('Sales and Use Tax'!$C$2:$C$1000=$A7)*(('Sales and Use Tax'!$D$2:$D$1000="Weekly Cleaning Service renewal")+('Sales and Use Tax'!$D$2:$D$1000="Biweekly Cleaning Service renewal")),0),"y",""),"")

Enter with Ctrl+Shift+Enter

Copy down

[D.Lovell]

What about doing it with COUNTIFS.... Put this in Q7 and copy it down...

Please Login or Register  to view this content.

I think though either way its prone to problems unless you restrict the input values with data validation or drop down lists to select from. You have one there that says "Weekly Cleaning Service Renewal and Pool Filter" for instance, and if anyone slightly miss types something etc... The formula is looking for the specific text so it has to match exactly or the "Y" won't come up.

Just thought I should point that out.

[D.Lovell]

What about doing it with COUNTIFS.... Put this in Q7 and copy it down...

Please Login or Register  to view this content.

I think though either way its prone to problems unless you restrict the input values with data validation or drop down lists to select from. You have one there that says "Weekly Cleaning Service Renewal and Pool Filter" for instance, and if anyone slightly miss types something etc... The formula is looking for the specific text so it has to match exactly or the "Y" won't come up.

Just thought I should point that out.

[D.Lovell]

You need to extend the ranges down to however many rows you want to account for by the way, I've only done them for the block of data on the worksheet.

[elitarvin]

JohnTopley, I tried your formula, but it would only work if I took out either "Weekly..." or "Biweekly..." and had only one in there, but then that takes away function or customers that I service weekly and biweekly.

D.Lovell, I believe that did the trick. I extended the ranges and specified the cells and tested it with "Weekly..." and "Biweekly..." and tried removing their names and it all functions as wanted!

Thanks so much for the help everyone! I'll be back if somethign acts fishy!