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

  • Thread starter Thread starter Lucky
  • Start date Start date
L

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
D6:D15 have a list with 3 options (complete,Cancel, Reschedule)

I need F18:F21 to sum up how many times B6:B15 match up with D6:D15 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"),--(D6:D15="Complete")) but * didn't work I get
a 0 value.

=SUMPRODUCT(--(B6:B15="HSO TC"),--(D6:D15="Complete")) worked but I also
have "Video TC" & "Phone TC" that need to be added to a generic TC category.
 
If all have TC at the end try=SUMPRODUCT(--(right(B6:B15,2)="TC"),--(D6:D15="Complete"))

or try this idea I use for Utilities
=SUMPRODUCT((ChecksC={"electricity","water","sewer","garbage"})*(ChecksD))
 
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!!!
 
Back
Top