Count 'cell containing...' in sumproduct

[superOK]

Hi

I am trying to create a summary table from a spreadsheet containing a lot of data, and I am a bit stuck on how to count cells in one column that contain a specific text string AND cells in the next column that contain a specific text string as part of the cell.

At the moment I am using SUMPRODUCT to count, for example, the number of rows in which A:A=D3 AND B:B=E2. The problem I have is that the cells in B:B sometimes contain E3 as part of a string. Can anyone please offer any advice as to how I can include these cells in the count?

Hopefully the attached example makes sense! What I want is for the 'SUMMARY TABLE' to read:

Product Version Total
P01 v1 3
P01 v2 2
P02 v1 2
P02 v2 1

Thanks in advance!

Ben

 

[Marcelo Branco]

Hi,

Maybe this

Formula in F3
=SUMPRODUCT(--($A$3:$A$8=D3),--(ISNUMBER(SEARCH(E3,$B$3:$B$8))))
copy down

HTH

M.

 

[superOK]

That works! Great! Thanks very much

 

[Marcelo Branco]

That works! Great! Thanks very much

You are welcome and tks for the feedback

M.