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

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.

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lucky" wrote in message
...
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.

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

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" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lucky" wrote in message
...
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.

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

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lucky" wrote in message
...
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" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lucky" wrote in message
...
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.