Formula that picks up part of a sentence in a cell

[ConfusedaboutVBA]

Hi all,

I wrote a formula that counts the number cells that contain “Yes” ($E$1 = Yes in the below formula). However sometimes the cell may contain more than just the word yes – for example ‘Yes – to be confirmed’ & my formula does not pick up this cell. Is there a way I can adjust the below formula to pick up the cell even if it has more than just the word in E1?

=SUMPRODUCT((Sheet2!$F$5:$F$200= $C3)*(Sheet2!$B$5:$B$200= $E$1))

Thanks

[ajryan88]

Sure,

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Let me know how this goes for you

[FDibbins]

or maybe...
=SUMPRODUCT((Sheet2!$F$5:$F$200= $C3)*(Sheet2!$B$5:$B$200= $E$1&"*"))

[ConfusedaboutVBA]

Thanks for your response AJ.

Did i enter it in correctly? It returns the error #VALUE! when i plug it into excel.

=SUMPRODUCT((Sheet2!$F$5:$F$200=$C3)*((MATCH(1,--(FIND($E$1,Sheet2!$B$5:$B$200)>0),0))))

[ConfusedaboutVBA]

Thanks FDibbins - but that formula doesn't pick up the cells that just have YES, is there a way the formula could pick up both cells with just the word 'Yes' & cells that have say "Yes - to be confirmed'?

Thanks guys

[FDibbins]

Upload a sample workbook please

[ajryan88]

Hi Confused,

Try the amended formula in my first post.

I hope you have a bit more luck with that one

[ConfusedaboutVBA]

Thanks guys - AJ your formula still returned #VALUE! =(

Attached is an example of what im trying to do, see how my formula doesn't pick up the Yes value in cell B6 on sheet2.

[RobertMika]

Are you using Excel 07?
Then
=COUNTIFS(Sheet2!$F$5:$F$200,$C3,Sheet2!$B$5:$B$200,E$1&"*")
Down and accross

[ajryan88]

Ah yes I see!

Try this then:

Please Login or Register  to view this content.

the drag the formula across and down as far as is necessary.

Hope this helps

[ConfusedaboutVBA]

Guys - you have been fantastic, thank you!

[ajryan88]

No worries,

Please don't forget to mark this thread as solved and click on the * nex to my post to say thanks