Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 28
Default SUMPRODUCT with condition FIND(text) is false

Using Excel 2007

I want to do a sumproduct where one of the conditions is that "Dedicated" is
NOT present in C:C. Dedicated can appear anywhere in the text and the text
varies, so I am using a FIND.

I am not sure how to do this? Here was my quess...

=SUMPRODUCT((A:A="GOLDMAN")*(B:B=1)*(FIND(C:C<"De dicated)))

Thanks,




  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default SUMPRODUCT with condition FIND(text) is false

Go Bucks!!! wrote:
Using Excel 2007

I want to do a sumproduct where one of the conditions is that "Dedicated" is
NOT present in C:C. Dedicated can appear anywhere in the text and the text
varies, so I am using a FIND.

I am not sure how to do this? Here was my quess...

=SUMPRODUCT((A:A="GOLDMAN")*(B:B=1)*(FIND(C:C<"De dicated)))

Thanks,






The syntax for FIND is as follows:

FIND(find_text,within_text,start_num)


I would use this in your SUMPRODUCT():

ISERROR(FIND("Dedicated",C:C))
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default SUMPRODUCT with condition FIND(text) is false

Using Excel 2007

Maybe this:

=COUNTIFS(A:A,"goldman",B:B,1,C:C,"<*dedicated*")

Note that an empty cell in column C will not contain dedicated so it could
be counted if the corresponding cells in columns A and B meet their
criteria.

--
Biff
Microsoft Excel MVP


"Go Bucks!!!" wrote in message
...
Using Excel 2007

I want to do a sumproduct where one of the conditions is that "Dedicated"
is
NOT present in C:C. Dedicated can appear anywhere in the text and the
text
varies, so I am using a FIND.

I am not sure how to do this? Here was my quess...

=SUMPRODUCT((A:A="GOLDMAN")*(B:B=1)*(FIND(C:C<"De dicated)))

Thanks,






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Excel Discussion (Misc queries) 2 September 28th 09 05:07 PM
SumProduct with Date Range and One Condition that is Text Iona Excel Worksheet Functions 4 September 18th 08 07:37 PM
Keep cell blank if condition is false in IF function ruthslaughter Excel Discussion (Misc queries) 2 November 23rd 05 02:15 PM
VB find (true false) Wildman Excel Worksheet Functions 6 May 6th 05 11:28 AM
How can I get a cell locked if the condition is false? FareedAhmed76 Excel Worksheet Functions 1 February 18th 05 02:49 PM


All times are GMT +1. The time now is 01:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"