Contains vs =

[Guest]

I used the wonderful feature “subtotal†adding a subtotal to column A at
every change of date, then I wanted to make a formula to calculate only the
Subtotal rows but when I wrote if A2 = “Totalâ€, it didn’t work because A2
said “10/06/2005 Total†not just “Totalâ€, I found away around, but I think it
would be a good idea you should add a feature if A2 'Contains' “Totalâ€.
Thanks a lot.


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Pete_UK]

If you use autofilter then you can choose Custom from the pull-down
list and this allows you to select Contains.

The SEARCH and FIND functions also allow you to look for text
contained within other text - perhaps you should incorporate these in
your formulae.

Hope this helps.

Pete

 

[Guest]

No need to add this feature - its already there. Choose Data, Filter,
AutoFilter
then open the dropdown in the column containing the subtotals and choose
Custom.
Open the first drop down and choose Contains, then enter Total in the second
box. Click OK.

 

[Guest]

Let me explain better my suggestion, I want my formula to look like this:
If A2 ‘Contains’ “Totalâ€,C2*G2,â€â€,
Then copy this formula all the way down,
so only the Total rows should do this multiplication.

 

[Guest]

Try

=SUMPRODUCT(--ISNUMBER(SEARCH("total",a1:a14)),b1:b14,C1:C14)

 

[Guest]

Hi Mottyb.

Given your example where the word "Total" occupies the last 5 characters of
your string, you could apply the following formula:

=if(right(A2,5)="Total",C2*G2,"")

HTH