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
=SUMPRODUCT(--(NOT(ISNUMBER(SEARCH("*U.S. Treasury*",D3:D782)))),E3:E782)
Life's a spreadsheet, Excel!
Say thanks, Click *
How can I add to this formula? I don't want *Fed* to be summed either.
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:
=SUMPRODUCT(E3:E782,--(ISERROR(SEARCH("U.S. Treasury",D3:D782))))
which can also be done with SUMIF by using those wildcards:
Formula:=SUMIF(D4:D782,"<>*U.S. Treasury*",E4:E782)
I assume you're using Excel 2007 or later, in which case:
Formula:=SUMIFS(E4:E782,D4:D782,"<>*U.S. Treasury*",D4:D782,"<>*Fed*")
For earlier versions, use this adpatation of Colin's formula
=SUMPRODUCT(E3:E782,--(ISERROR(SEARCH("U.S. Treasury",D3:D782))),--(ISERROR(SEARCH("Fed",D3:D782))))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks