| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting Using Multiple Criteria | Excel Worksheet Functions | |||
| Counting by multiple criteria | Excel Worksheet Functions | |||
| counting using multiple criteria | Excel Discussion (Misc queries) | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
