Help with combining 2 formulas

Hello guys,
How can I combine this formula
SUMPRODUCT(COUNTIFS(F3:F4000,{"METCO*";"AMDRY*";"TAFA*";"DURABRADE*";"DIAMALLOY*";"DFL*"},G3:G4000,">="&S4,G3:G4000,"<"&EDATE(S4,1)))

To this
=SUMPRODUCT(--(E3:E4000<>""),LEN(E3:E4000)-LEN(SUBSTITUTE(E3:E4000,",",""))+1,(G3:G4000>="01/01/17"+0)*(G3:G4000<="31/01/17"+0))


Example of what I am trying to do (The table can be found below)

E F G S T
3:TS004,005,006 METCO 58NS 03/01/17 Month Count
4:TS007,008 HARDNESS 20/01/17 January 6
5:TS009,0010,0011 AMDRY 52 31/01/17

By combing the formula, I hope that the formula will calculate a comma as 2 and it will calculate only when the criteria METCO,AMDRY,TAFA,DURABRADE and DIAMALLOY is met and also it will only the month of data that I need.




Thanks in advance

welcome to the forum. do upload an excel sample so that we do not have to manually key in your data to do a testing. input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

it seems like this would work for you:

Formula:

Please Login or Register  to view this content.

i used an assumption that you want to find these key words inside column F though. if it needs to begin with these keywords (as you used "METCO*"), this formula would not work.

Hi,
The formula gives a value of 0 instead of 9.
9 when the formula counts the E cell with criteria of METCO, TAFA, DFL, AMDRY, and DURABRADE and the month
i have uploaded a sample as instructed by you.

thanks for your help in advanced.

you forgot to change the part in red here:

Formula:

Please Login or Register  to view this content.

but anyway, i realized i duplicated that part, so you may remove it. the wildcard (asterisk) has no purpose here too. and i forgot to change the semi-colons to commas. so:

Formula:

Please Login or Register  to view this content.

and here's how you can improve on the sample file to make it easier to understand too:

do upload an excel sample so that we do not have to manually key in your data to do a testing. input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

desired results: 9?
if you look at my signature for the url, you also see that you can explain how to get 9. i am guessing:
count row 1 as 3 counts because there are 3 items in column A, column F contains METCO. column C is within jan 2017.
count row 3 as 1 count. 1 item in column A, column F contains AMDRY. column C is within jan 2017.
count row 6 as 2 counts because there are 2 items in column A, column F contains AMDRY. column C is within jan 2017.
count row 7 as 1 count. 1 item in column A, column F contains TAFA. column C is within jan 2017.
count row 8 as 2 counts because there are 2 items in column A, column F contains DURABRADE. column C is within jan 2017.
total of 3 + 1 + 2 + 1 + 2

if that answers your question, do mark your thread as "Solved"