SUMIFS but for text

[Declamatory]

Hi Folks,

I have the following formula:

=IF(OR(SUMIF(Dealing!D:D,"A800F",Dealing!G:G)<>0,SUMIF(Dealing!D:D,"C800F",Dealing!G:G)<>0),"Estimate",IF(AND(B14="",E14=""),"No dealing for "&TEXT(A11,"dd/mm/yyyy"),"Confirmed"))

The cell with the formula is in a worsksheet called "Instruction" and the formula is looking at a worksheet called "Dealing" and looking in column D to see if there is a value of either A800F or C800F and where the corresponding cell in column G has a value of anything other than zero. If the result is true then the value will be "Estimate". If the result isn't true then it looks to see if the values in both Cell B14 and E14 in "Instruction" worksheet are blank. If so the result is "No dealing for" and then a date captured in cell A11. Where neither is true then the result will be "Confirmed".

I hope that explains where I'm at.

What I also need the formula to do (or at least bring through an answer in another cell ignoring the above formula) is to look in the dealing worksheet at column D for text "A800F" or "C800F" and check it the corresponding cell in column W in the dealing worksheet contains the word "Purchase". Where both of these are true then bring through a result of "Estimate"

This formula forms part of a macro where the formula in questions currently looks like:

ActiveCell.FormulaR1C1 = _
        "=IF(OR(SUMIF(Dealing!C[3],""A800F"",Dealing!C[6])<>0,SUMIF(Dealing!C[3],""C800F"",Dealing!C[6])<>0),""Estimate"",IF(AND(R[-3]C[1]="""",R[-3]C[4]=""""),""No dealing for ""&TEXT(R[-6]C,""dd/mm/yyyy""),""Confirmed""))"

 

[Jonmo1]

Try

=IF(SUM(COUNTIFS(Dealing!D:D,{"A800F","C800F"},Dealing!W:W,"Purchase"))>0,"Estimate",IF(AND(B14="",E14=""),"No dealing for "&TEXT(A11,"dd/mm/yyyy"),"Confirmed"))

 

[Declamatory]

Thanks for the reply Jonmo1. I thought that you may have done it but if there is a value in column G greater or less than zero in the dealing worksheet then the end result should be estimate.

 

[Jonmo1]

Perhaps

=IF(SUM(SUMIFS(Dealing!G:G,Dealing!D:D,{"A800F","C800F"},Dealing!W:W,"Purchase"))<>0,"Estimate",IF(AND(B14="",E14=""),"No dealing for "&TEXT(A11,"dd/mm/yyyy"),"Confirmed"))