SUMPRODUCT with "Contains"

[WildWill]

I am using this formula: =SUMPRODUCT(('Staffing Data'!E8:E9082=S3)*('Staffing Data'!P8:P9082='Staffing Report'!O6)*('Staffing Data'!AA8:AA9082="Active")).

I want to change the last component (AA8:AA9082="Active") to find any cell which contains the word "Active" but could contain other text characters. How do I change the syntax for this?

 

[energman58]

Not sure what you are trying to do here - if you want to count all the matches of rows where teh values are S3 O6 and include "Active" in the last criteria than this should do it :

=SUMPRODUCT(--('Staffing Data'!E8:E9082=S3),--('Staffing Data'!P8:P9082='Staffing Report'!O6),--(isnumber(find("Active",'Staffing Data'!AA8:AA9082)))

FIND is case sensitive - SEARCH isnt so would include inactive or active as a match when FIND would not; both would match with Active yoghurt or Activeman. Search can also include wild cards ? and * if that helps any (? means any single character and * means any one or more character). Both return an error (#VALUE) if they cant find the search string hence ISNUMBER returns FALSE

 

[MikeWx]

You can also replace your third component with:

countif('Staffing Data'!AA8:AA9082,"*Active*")

This will count any occurrence of Active or active in any cell in the AA8 through AA9802 range.

 

[WildWill]

Thanks Guys!!! both comments highly appreciated. One more related question: how do I structure SUMPRODUCT syntax to combine AND as well as OR functions: E.g If I want a SUMPRODUCT to count all cells where A1:A10="Blue", B1:B10="X" and C1:C10="B" or "C"...how would I achieve this?

 

[RoryA]

If you only have one OR condition:
=SUMPRODUCT((A1:A10="Blue")*(B1:B10="X")*(C1:C10={"B","C"}))

or you can use:
=SUMPRODUCT((A1:A10="Blue")*(B1:B10="X")*((C1:C10="B")+(C1:C10="C")))

 

[WildWill]

Thanks Rory!!!