| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if with multiple column criteria
I have multiple columns in a spreadsheet and I need to count the number times
column D contains €œTBN€ when column L is equal to 1 Thanks. -- JPS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if with multiple column criteria
Maybe something like this in say, M2:
=SUMPRODUCT((D2:D10="TBN")*(L2:L10=1)) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "JPS" wrote: I have multiple columns in a spreadsheet and I need to count the number times column D contains €œTBN€ when column L is equal to 1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if with multiple column criteria
Max,
Works great when column D is only "TBN" how do I add criteria when D conatins TBN-July or TBNamed -- JPS "Max" wrote: Maybe something like this in say, M2: =SUMPRODUCT((D2:D10="TBN")*(L2:L10=1)) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "JPS" wrote: I have multiple columns in a spreadsheet and I need to count the number times column D contains €œTBN€ when column L is equal to 1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if with multiple column criteria
This then, in M2:
=SUMPRODUCT((ISNUMBER(SEARCH("TBN",D2:D10))*(L2:L1 0=1))) Replace SEARCH with FIND if you need it to be a stricter, case sensitive search -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "JPS" wrote: Max, Works great when column D is only "TBN" how do I add criteria when D contains TBN-July or TBNamed |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if with multiple column criteria
Try
=SUMPRODUCT((LEFT(D2:D10,3)="TBN")*(L2:L10=1)) Mike "JPS" wrote: Max, Works great when column D is only "TBN" how do I add criteria when D conatins TBN-July or TBNamed -- JPS "Max" wrote: Maybe something like this in say, M2: =SUMPRODUCT((D2:D10="TBN")*(L2:L10=1)) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "JPS" wrote: I have multiple columns in a spreadsheet and I need to count the number times column D contains €œTBN€ when column L is equal to 1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if with multiple column criteria
Perfecto... Thanks
-- JPS "Max" wrote: This then, in M2: =SUMPRODUCT((ISNUMBER(SEARCH("TBN",D2:D10))*(L2:L1 0=1))) Replace SEARCH with FIND if you need it to be a stricter, case sensitive search -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "JPS" wrote: Max, Works great when column D is only "TBN" how do I add criteria when D contains TBN-July or TBNamed |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if with multiple column criteria
Mike,
Worked... Thanks for the solution -- JPS "Mike H" wrote: Try =SUMPRODUCT((LEFT(D2:D10,3)="TBN")*(L2:L10=1)) Mike "JPS" wrote: Max, Works great when column D is only "TBN" how do I add criteria when D conatins TBN-July or TBNamed -- JPS "Max" wrote: Maybe something like this in say, M2: =SUMPRODUCT((D2:D10="TBN")*(L2:L10=1)) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- "JPS" wrote: I have multiple columns in a spreadsheet and I need to count the number times column D contains €œTBN€ when column L is equal to 1 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if with multiple column criteria
"JPS" wrote:
Perfecto... Thanks Welcome. Take a moment to press the "Yes" button below from where you're reading this. -- Max Singapore http://savefile.com/projects/236895 Downloads:17,700 Files:359 Subscribers:55 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
| COUNT and SUM with multiple criteria | Excel Discussion (Misc queries) | |||
| Count with multiple column criteria | Excel Discussion (Misc queries) | |||
| Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
| Count on Multiple Criteria over whole column | Excel Worksheet Functions | |||
Linear Mode
