SumProduct Help

[penfolda]

Hi,

Please can you help me adapt my formula so that the second part of the sum product is looking for any occurrence of "Ticket" within a string from E2:E995. The reason being is that in the data, "Ticket" is suffixed with a number e.g. "Ticket1", "Ticket2" etc.

=IF(ISBLANK(A2),"",IF(SUMPRODUCT((Details!$A$2:$A$995=Summary!A2)*(Details!$E$2:$E$995="Ticket"))>0,"Yes","No"))

Thanks

[Ace_XL]

Try

=IF(ISBLANK(A2),"",IF(SUMPRODUCT((Details!$A$2:$A$995=Summary!A2)*(ISNUMBER(MATCH("Ticket*",Details!$E$2:$E$9950,0))))>0,"Yes","No"))

[penfolda]

Hi thanks for the response. Your suggestion doesn't seem to be identifying only cases with INC e.g. it is basically giving me a full count of where A2:A995=A* (the first part of the SUMPRODUCT).

Thanks

[Ace_XL]

My apologies..try

=IF(ISBLANK(A2),"",IF(SUMPRODUCT((details!$A$2:$A$995=Summary!A2)*(LEFT(details!$E$2:$E$995,6)="Ticket")),"Yes","No"))

[penfolda]

Hi, Sorry, I still can't get it to work. I have attached an example with the formula (i have just left in the sumproduct part).

Thanks for your help.

[XOR LX]

Hi,

This looks like it's related to the other post I helped you with, correct?

http://www.excelforum.com/excel-form...umna-or-b.html

What precisely are you trying to do here? Sum all the numerical values in column A which have a corresponding "Ticket..." entry in column E but which also fall under the relevant "Issue..." header? If so, a 'standard' SUMPRODUCT solution will not do.

Regards

[penfolda]

Hi,

Related in terms of the same project but different in terms of requirement.

This is a straight forward SUMPRODUCT. The only difference to the standard is that I want to count where e.g. Sheet1!A1:A10=Sheet2!A1 and Sheet1!B1:B10="Ticket*" - as "Ticket" is a prefix to a number in a single string, I need to be able to identify "Ticket" by the first 6 characters in B1:B10.

Thanks

[XOR LX]

But that's what I mean about it being 'non-standard'.

There are precisely zero rows in your current set-up for which there is an entry in column A containing "Issue" AND an entry in column E containing "Ticket*". That's why I asked if perhaps you weren't interested in the cells below each of those containing "Issue" in column A.

Regards