FIND subtext and SUM corresponding count

[zarlengp]

Hello,

I have the following data:

A B
01-EJ-01 1
01-TC-02 2
01-EJ-03 3
01-BX-04 4
01-EJ-04 5

I am trying to find all cells in column A that contain "EJ". Then I need to sum their corresponding count in column B. In this example the answer should be 9. What is the function to do this????

[zbor]

Here, try this:

=SUMIF(A:A, "*EJ*", B:B)

[zarlengp]

Here, try this:

=SUMIF(A:A, "*EJ*", B:B)

Thank you very much!!! Why did you put the ** in the formula?

[zbor]

EJ will look for exact EJ
*EJ will look for all words that ends with EJ
*EJ* will look for all words that contains EJ

[zarlengp]

Awesome. Good to know.

[Marcol]

Possibly this

Assuming your data begins in A2
In C2

Please Login or Register  to view this content.

in B2

Please Login or Register  to view this content.

Drag/Fill both columns down

Copy the result and paste special > values to D2 then remove duplicates from Colums D:E

Hope this helps

[EDIT]
Or to add to zbors' suggestion

Please Login or Register  to view this content.