counting with multiple criteria and wildcards
 

I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

Try this:

=SUMPRODUCT(--(A1:A5="Printed"),--(LEFT(B1:B5,2)="gt"))

Biff

"Jorgen Jansson" wrote in message
...
I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way
around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

Try this:

=SUMPRODUCT((A1:A5="Printed")*(LEFT(B1:B5,2)="gt") )


"Jorgen Jansson" wrote:

I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

Try this

=SUMPRODUCT(--(A1:A5="Printed"),--(LEFT(B1:B5,2)="gt"))

Commit with CTRL SHIFT ENTER. You should see {} around it when you are done.

HTH,
Barb Reinhardt

"Jorgen Jansson" wrote:

I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

This might do well enough, based on your sample data as posted:
=SUMPRODUCT((A1:A5="Printed")*(LEFT(B1:B5,2)="gt") )

A more generic fuzzy to use would be:
=SUMPRODUCT((A1:A5="Printed")*(ISNUMBER(SEARCH("gt ",B1:B5))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jorgen Jansson" wrote:
I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

Commit with CTRL SHIFT ENTER. You should see {} around it when you are
done.

Don't think it's necessary to array-enter, unless TRANSPOSE is used within
the sumproduct
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

counting with multiple criteria and wildcards
 

Works a treat! Thanks to all who replied!

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Printed"),--(LEFT(B1:B5,2)="gt"))

Biff

"Jorgen Jansson" wrote in message
...
I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way
around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

You're welcome. Thanks for the feedback!

Biff

"Jorgen Jansson" wrote in message
...
Works a treat! Thanks to all who replied!

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A5="Printed"),--(LEFT(B1:B5,2)="gt"))

Biff

"Jorgen Jansson" wrote in
message
...
I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and
starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way
around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

I tried your generic fuzzy approach and it worked perfect, but how would you
count the number of cells that didn't have "gt"?

"Max" wrote:

This might do well enough, based on your sample data as posted:
=SUMPRODUCT((A1:A5="Printed")*(LEFT(B1:B5,2)="gt") )

A more generic fuzzy to use would be:
=SUMPRODUCT((A1:A5="Printed")*(ISNUMBER(SEARCH("gt ",B1:B5))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jorgen Jansson" wrote:
I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

Hi,

Try this

SUMPRODUCT((A1:A5="Printed")*(LEFT(B1:B5,2)<"gt") )
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"jeq214" wrote in message
...
I tried your generic fuzzy approach and it worked perfect, but how would
you
count the number of cells that didn't have "gt"?

"Max" wrote:

This might do well enough, based on your sample data as posted:
=SUMPRODUCT((A1:A5="Printed")*(LEFT(B1:B5,2)="gt") )

A more generic fuzzy to use would be:
=SUMPRODUCT((A1:A5="Printed")*(ISNUMBER(SEARCH("gt ",B1:B5))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jorgen Jansson" wrote:
I've got a list:

Job Status Job Name
Printed gt0307poster final.pdf
Printed gt0407poster_final.pdf
Error cs0207expert_03.pdf
Canceled gt0507poster final.pdf

I want to count number of items that has Job status "Printed" and
starts
with the letters "gt". I've tried with a sumproduct formula:

=SUMPRODUCT((A1:A5="Printed")*(B1:B5="gt*"))

But it doesn't seem to work with the wildcard character "*". Any way
around
this?

Thanks in advance

counting with multiple criteria and wildcards
 

"jeq214" wrote:
I tried your generic fuzzy approach and it worked perfect, but how would you
count the number of cells that didn't have "gt"?

Try replacing ISNUMBER with ISERROR in the fuzzy
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---