count entries that equal one criteria if another column meets anot

  • Thread starter Thread starter ccKennedy
  • Start date Start date
C

ccKennedy

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?
 
=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/2005/06/23/download-pivottable-parameters/

best wishes
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
 
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!
 
i have, but my workbook has blank cells & it's my understanding that the
pivot table won't support blank cells...
 
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...
 
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."
 
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.
 
Back
Top