LinkBack URL
About LinkBacks
I want the current formula to sum everything that does not have *U.S. Treasury* in it. I need the SEARCH function in it to search for the alpha characters.
SUMPRODUCT(--(E3:E782),--(ISNUMBER(SEARCH("*U.S. Treasury*",D3:D782))))
I want the current formula to sum everything that does not have *U.S. Treasury* in it. I need the SEARCH function in it to search for the alpha characters.
SUMPRODUCT(--(E3:E782),--(ISNUMBER(SEARCH("*U.S. Treasury*",D3:D782))))
Maybe
Please Login or Register to view this content.
Life's a spreadsheet, Excel!
Say thanks, Click *
Do you mean?
=SUMPRODUCT(--(NOT(ISNUMBER(SEARCH("*U.S. Treasury*",D3:D782)))),E3:E782)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
You don't need the * wildcards. SEARCH already looks for a substring.
Formula:
Please Login or Register to view this content.
which can also be done with SUMIF by using those wildcards:
Formula:Please Login or Register to view this content.
How can I add to this formula? I don't want *Fed* to be summed either.
I assume you're using Excel 2007 or later, in which case:
Formula:Please Login or Register to view this content.
For earlier versions, use this adpatation of Colin's formula
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks