Problems creating a result based on 2 cells being true..

[Lucky]

I need Cell 3 to count how many times Cell 2 & 3 return a true value. the
problem

example:
B6:B15 have a list validation with 7 options
D615 have a list with 3 options (complete,Cancel, Reschedule)

I need F18:F21 to sum up how many times B6:B15 match up with D615 saying
"complete"

The other problem is I need to use a wildcard to count 3 types of items into
1 generic category.

here's what I tried so far....
=SUMPRODUCT(--(B6:B15="* TC"),--(D615="Complete")) but * didn't work I get
a 0 value.

=SUMPRODUCT(--(B6:B15="HSO TC"),--(D615="Complete")) worked but I also
have "Video TC" & "Phone TC" that need to be added to a generic TC category.

 

[Don Guillett]

If all have TC at the end try=SUMPRODUCT(--(right(B6:B15,2)="TC"),--(D615="Complete"))

or try this idea I use for Utilities
=SUMPRODUCT((ChecksC={"electricity","water","sewer","garbage"})*(ChecksD))

 

[Lucky]

The first solution was really helpful. the 2nd 1 would be alot more time
consuming cause of all the options I'd have to list.

Thanx for the helpful response!!!

 

[Don Guillett]

Glad to help