| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count entries that equal one criteria if another column meets anot
hi,
i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets anot
=SUMPRODUCT(--(A1:A100="Product"),--(B1:B100="Jan"))
I am assuming Jan is text not a formatted date You can use cell references =SUMPRODUCT(--(A1:A100=K2),--(B1:B100=L1)) where K1 holds "Product" and L1 hold "Jan" (without quotes, of course) If this is a big, serious project, this may be the time to learn about pivot tables http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...le-parameters/ best wishes Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "ccKennedy" wrote in message ... hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets anot
ccKennedy;197064 Wrote: hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? Hi, have you tried a Pivot Table? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54296 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets
Thank you! what do the dashes do? i also tried without the dashes and that
worked, i just had to make "product" a cell reference and "Jan" the text with quotes. have tried Pivot tables, but no luck - i do have blank cells in teh worksheet, so it won't read properly... too bad. thanks so much for your reply! "Bernard Liengme" wrote: =SUMPRODUCT(--(A1:A100="Product"),--(B1:B100="Jan")) I am assuming Jan is text not a formatted date You can use cell references =SUMPRODUCT(--(A1:A100=K2),--(B1:B100=L1)) where K1 holds "Product" and L1 hold "Jan" (without quotes, of course) If this is a big, serious project, this may be the time to learn about pivot tables http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...le-parameters/ best wishes Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "ccKennedy" wrote in message ... hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets
i have, but my workbook has blank cells & it's my understanding that the
pivot table won't support blank cells... "Pecoflyer" wrote: ccKennedy;197064 Wrote: hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? Hi, have you tried a Pivot Table? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54296 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets anot
ccKennedy;197110 Wrote: i have, but my workbook has blank cells & it's my understanding that the pivot table won't support blank cells... "Pecoflyer" wrote: ccKennedy;197064 Wrote: hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? Hi, have you tried a Pivot Table? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'count entries that equal one criteria if another column meets anot - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=54296) Pivot table supports blank cells, but they will not allow you to group. As for the -- it is called an unary operator and coerces True/False outcomes to 1/0 thus allowing the SUMPRODUCT fi-unction to work -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54296 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets
thank you
i'm using the sumproduct function, but it's now taking almost a minute to calculate after i enter the data in the cell - says "calculating" and the status percentage at the bottom - is this normal? perhaps i should revisit pivot tables... "Pecoflyer" wrote: ccKennedy;197110 Wrote: i have, but my workbook has blank cells & it's my understanding that the pivot table won't support blank cells... "Pecoflyer" wrote: ccKennedy;197064 Wrote: hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? Hi, have you tried a Pivot Table? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'count entries that equal one criteria if another column meets anot - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=54296) Pivot table supports blank cells, but they will not allow you to group. As for the -- it is called an unary operator and coerces True/False outcomes to 1/0 thus allowing the SUMPRODUCT fi-unction to work -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54296 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets
For a full explanation visit the site of J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "ccKennedy" wrote in message ... Thank you! what do the dashes do? i also tried without the dashes and that worked, i just had to make "product" a cell reference and "Jan" the text with quotes. have tried Pivot tables, but no luck - i do have blank cells in teh worksheet, so it won't read properly... too bad. thanks so much for your reply! "Bernard Liengme" wrote: =SUMPRODUCT(--(A1:A100="Product"),--(B1:B100="Jan")) I am assuming Jan is text not a formatted date You can use cell references =SUMPRODUCT(--(A1:A100=K2),--(B1:B100=L1)) where K1 holds "Product" and L1 hold "Jan" (without quotes, of course) If this is a big, serious project, this may be the time to learn about pivot tables http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...le-parameters/ best wishes Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "ccKennedy" wrote in message ... hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets
Hi,
Pivot Tables are designed to group your data in ways you can't even do in the spreadsheet! So I'm not sure what you mean by this "Pivot table supports blank cells, but they will not allow you to group." -- If this helps, please click the Yes button Cheers, Shane Devenshire "Pecoflyer" wrote: ccKennedy;197110 Wrote: i have, but my workbook has blank cells & it's my understanding that the pivot table won't support blank cells... "Pecoflyer" wrote: ccKennedy;197064 Wrote: hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? Hi, have you tried a Pivot Table? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'count entries that equal one criteria if another column meets anot - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=54296) Pivot table supports blank cells, but they will not allow you to group. As for the -- it is called an unary operator and coerces True/False outcomes to 1/0 thus allowing the SUMPRODUCT fi-unction to work -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54296 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
count entries that equal one criteria if another column meets
Hi,
Most assuredly, you should revisit the pivot table option: 1. Select your data with one row of titles. 2. Choose Data, Pivot Table and Pivot Chart Report, Finish 3. Drag the Month field into the row area, 4. Drag the Category field into the row area also (I am assuming the name of the column with Personal, Product and so on, is Category) 5. Drag the Category field into the Data area. -- If this helps, please click the Yes button Cheers, Shane Devenshire "ccKennedy" wrote: thank you i'm using the sumproduct function, but it's now taking almost a minute to calculate after i enter the data in the cell - says "calculating" and the status percentage at the bottom - is this normal? perhaps i should revisit pivot tables... "Pecoflyer" wrote: ccKennedy;197110 Wrote: i have, but my workbook has blank cells & it's my understanding that the pivot table won't support blank cells... "Pecoflyer" wrote: ccKennedy;197064 Wrote: hi, i'd like to use the countif feature but only if another column meets another criteria. Example: A B Product Jan Personnel Feb so, i want to know how many times "Product" occurred in Jan, how many times "Personnel" occurred in Feb, etc. help? Hi, have you tried a Pivot Table? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: 'The Code Cage Forums - View Profile: Pecoflyer' (http://www.thecodecage.com/forumz/me...pecoflyer.html) View this thread: 'count entries that equal one criteria if another column meets anot - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=54296) Pivot table supports blank cells, but they will not allow you to group. As for the -- it is called an unary operator and coerces True/False outcomes to 1/0 thus allowing the SUMPRODUCT fi-unction to work -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54296 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count entries based on criteria in a different column | Excel Worksheet Functions | |||
| count entries based on criteria in a different column | Excel Worksheet Functions | |||
| Need to report a count that meets multiple criteria. | Excel Worksheet Functions | |||
| How do I count in column A when it meets all criteria in three col | Excel Worksheet Functions | |||
| how can i count a number that meets a criteria? | Excel Worksheet Functions | |||
Linear Mode
